Count with Aggr()

[#1]

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

)

[Anonymous]

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

[#3]

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

)

[#4]

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.

[#5]

Hi Vishwaranjan,

I think you expression produces the same result as mine.