topic count distinct IF sum in QlikView

count distinct IF sum

Wed, 20 Apr 2011 09:53:51 GMT

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

count distinct IF sum

Wed, 20 Apr 2011 10:06:19 GMT

Bro ,

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

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

count distinct IF sum

Wed, 20 Apr 2011 10:14:16 GMT

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.

count distinct IF sum

pat_agen — Wed, 20 Apr 2011 10:17:04 GMT

hi,

use the aggr function for this:

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

hope this helps.

count distinct IF sum

Wed, 20 Apr 2011 10:25:23 GMT

Thanks, but still not working....

count distinct IF sum

Wed, 20 Apr 2011 10:37:59 GMT

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.

SV:Re: count distinct IF sum

ToniKautto — Wed, 20 Apr 2011 10:39:27 GMT

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) )

SV:Re: count distinct IF sum

Wed, 20 Apr 2011 16:43:42 GMT

I would go with:

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