I have a formula to count the number of unique rec...
# generall
Luis
11/22/2021, 12:45 AMI have a formula to count the number of unique records and multiply it by a certain number in saved search. Its summary type is Sum. It seems to be working.
COUNT(DISTINCT(CASE WHEN {type} = 'Invoice' THEN {createdfrom} ELSE NULL END)) * (1.79+0.45)
HOWEVER, when I use it in another formula since I need to add this to another number, it's no longer working.
m
michoel
11/22/2021, 12:59 AMTry changing the summary type to max
l
Luis
11/22/2021, 2:27 PMThank you. I tried using Maximum for this formula but it didn't work. It says invalid expression.
Luis
11/22/2021, 2:27 PMSUM(CASE WHEN {type} IN ('Invoice','Credit Memo') THEN {amount} ELSE 0 END)-SUM(NVL({cogsamount},0))-SUM(CASE WHEN {type} = 'Item Fulfillment' AND {item.type} IN ('Inventory Item','Kit/Package') THEN {quantity}*0.65 ELSE 0 END)+SUM(COUNT(DISTINCT(CASE WHEN {type} = 'Invoice' THEN {createdfrom} ELSE NULL END)) * (1.79+0.45))
Luis
11/22/2021, 2:28 PMI also tried getting rid of the SUM for each operation but I got an "expected error".
Luis
11/22/2021, 2:31 PMOn its own (summary type = sum), this works. But once I add or deduct something from it, it breaks.
COUNT(DISTINCT(CASE WHEN {type} = 'Invoice' THEN {createdfrom} ELSE NULL END)) * (1.79+0.45)
2 Views