Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table of customers who have bought things, and some of whom have returned things for a refund. I want to count the number of customers who have a net positive transaction value;
Row Number Customer Transaction Value
1 CustomerA $1000
2 CustomerA -$1000
3 Customer B $500
Should give the answer "One Customer"
Count(Distinct {<[Transaction Value] = {">0"}>} [Customer] ) will give the answer "2" because it will count Row 1 & Row 3.
I have tried Count(Distinct {<Aggr (Sum ([Transaction Value])) = {">0"}>} [Customer] ) but that gives an error in the formula editor.
I also tried, Count(Distinct {<'Sum ([Transaction Value]) >0'>} [Customer] ) which is accepted, but gives the wrong answer (I think it just counts all records
Any clues???
Thanks.
Try this
Count(DISTINCT {<Customer = {"=Sum([Transaction Value]) >0"}>} Customer)
Try this
Count(DISTINCT {<Customer = {"=Sum([Transaction Value]) >0"}>} Customer)
That works a treat, thanks!
Done
what if i want a range here e.g my expression is: Count(DISTINCT {<[KYSC.FLOAT_DAY] = {"=AVG([KYSC.FLOAT_DAY])>=0"}>} [KYSC_SALE_CHNL_CD])
also does it work with avg?
my range is AVG([KYSC.FLOAT_DAY])>=0<1.
thanks.
Wajiha