topic Re: Count with Aggr() in QlikView

Count with Aggr()

Mon, 12 Aug 2013 07:49:21 GMT

I am trying to count the numbers of IDs where certain measures satisfied required conditions. But I am unable to get the right number, can someone point me to the right direction here. Below is my current code..

pick(match(d,'M1','M2','M3','M4')

,count(if(aggr(sum(M1),ID,Dim_01)>0,ID))

,count(if(aggr(sum(M2),ID,Dim_01)>0,ID))

,count(if(aggr(sum(M3),ID,Dim_01)>0,ID))

,count(if(aggr(sum(M4),ID,Dim_01)>0,ID))

)

Re: Count with Aggr()

Anonymous — Mon, 12 Aug 2013 12:15:56 GMT

The numbers in your report shows count of distinct IDs . You could use NODISTINCT in the aggr function to get every ID provided your if condition is met.

pick

(match(d,'M1','M2','M3','M4')

,count(if(aggr(NODISTINCT sum(M1),ID,Dim_01)>0,ID))

,count(if(aggr(NODISTINCT sum(M2),ID,Dim_01)>0,ID))

,count(if(aggr(NODISTINCT sum(M3),ID,Dim_01)>0,ID))

,count(if(aggr(NODISTINCT sum(M4),ID,Dim_01)>0,ID))

)

I hope this helps!

MultiView

Re: Count with Aggr()

Mon, 12 Aug 2013 12:28:17 GMT

try this

pick(match(d,'M1','M2','M3','M4')

,count(distinct if(aggr(sum(M1),ID,Dim_01)>0,ID))

,count(distinct if(aggr(sum(M2),ID,Dim_01)>0,ID))

,count(distinct if(aggr(sum(M3),ID,Dim_01)>0,ID))

,count(distinct if(aggr(sum(M4),ID,Dim_01)>0,ID))

)

Re: Count with Aggr()

Tue, 13 Aug 2013 03:59:17 GMT

Hi MulitView,

thank you for your help.

What if I would like to count the number of distincts ID? I don't quite understand why I get 0 on some of the line items.

Re: Count with Aggr()

Tue, 13 Aug 2013 04:00:09 GMT

Hi Vishwaranjan,

I think you expression produces the same result as mine.