count distinct IF sum

[#1]

Hi,

I'm creating a line chart with month, and count().

count ( distinct Customer_Code)

How can i do the distinct count with IF statement. says count distinct customer if they sales is more than certain amount?

I tried :

if (sum(sales) <> 0 , count ( distinct Customer_Code))

But it's not working. and SET can't do sum() within it. any other alternatives?

Thanks

[#2]

Bro ,

You can try this...........

=

count({$<sum(SalesDetail_Amt) <>{'0'}>} distinct CUSTOMER_ID )

[#3]

Hi Dushyant,

Thanks for the suggestion. But i think in SET, we can't use functions like sum().

I'm not sure how true, but it doesn't work for me.

[pat_agen]

hi,

use the aggr function for this:

Count (distinct if(aggr(sum(Sales),CustomerId)>0, CustomerId))

hope this helps.

[#5]

Thanks, but still not working....

[#6]

in fact, i need to do <> '0'.

funny thing is, i can get the correct count when i do = '0'

but wrong count for <> ' 0 '

in my data, there are negative values , positive values , and 0.

[ToniKautto]

I'm a bit unsure if the previous aggr-function works, since the aggr will return a data set and not a numeric value. Try adding an additional sum of the aggr to make the result numerical.

Count ( distinct if( sum ( aggr ( sum(Sales), CustomerId) ) >0, CustomerId) )

[#8 · Accepted solution]

I would go with:

count(distinct aggr(if(sum(Sales)>100000,CustomerId),CustomerId))