Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following data. I'm trying to calculate a distinct count for say 202001 where the sum of the values per id is not equal to zero.
So the answer for 202001 would be 1.
202002 would be 2 etc if I selected each month
Month | ID | Type | Value | |
202001 | 1 | 2 | 10 | |
202001 | 1 | 4 | -10 | |
202001 | 2 | 2 | 20 | |
202001 | 2 | 4 | 30 | |
202002 | 1 | 2 | 40 | |
202002 | 2 | 4 | 20 | |
202002 | 2 | 2 | 20 | |
202003 | 1 | 2 | 10 | |
202003 | 2 | 2 | 20 | |
202003 | 3 | 2 | 30 | |
202003 | 3 | 2 | 20 | |
I currently have something like this,
count(Aggr(distinct if([Month]=addmonths(vMaxRollMth,-1) and (Total <Month,ID>Value)<>0,[ID]),[ID]))
but it is not working.
any assistance much appreciated
You can try with
=count({<ID = {"=sum(Value) <>0"}>} distinct ID )
Thanks, it however seems to count all counts for an ID and not separate by month?
Hi JustinM,
I think you can use the formula proposed by qv_testing or maybe this
Count({ $<ID = {"=Sum(Value*ID)<>0"} > }Distinct ID)
you can create a straight table with month as dimension and the formula above as expression