General Notice: BOB is going to retire...please see details here. General Notice: No events within the next 45 days. 
Author 
Message 
MBLM Principal Member
Joined: 31 Jan 2014
Posts: 210

Posted: Wed Oct 07, 2015 5:33 am Post subject: Running Distinct Count 


In each month I have ID's. When I do a distinct count I get the right number by month.
But what is essential is how the number of distinct ID's grows during the year.
If this is the data
Month ID
Jan A
Feb A
Feb B
Mar C
Mar D
Apr A
Apr D
This should be the result
Month CumdistID
Jan 1 (A)
Feb 2 (A, B)
Mar 4 (A, B, C, D)
Apr 4 (A, B, C, D)
I can do a Count([ID]) In([Year]) but that would give me the total for the year in each row. And I need the growth so it should be something like Where([Month]<=[Month] 

Back to top 


MBLM Principal Member
Joined: 31 Jan 2014
Posts: 210

Posted: Fri Oct 09, 2015 2:35 am Post subject: Re: Running Distinct Count 


Well, solved myself. Or better, created a "workaround".
In a Graph where I have months at the category axis and year as region, this gives me the correct result:
Code:  RDC_ID=
If [Month]=1 Then Count([ID]) Else
If [Month]=2 Then Count([ID] Where([Month]<=2)) ForAll[Month]) Else
If [Month]=3 Then Count([ID] Where([Month]<=3)) ForAll[Month]) Else
If [Month]=4 Then Count([ID] Where([Month]<=4)) ForAll[Month]) Else
If [Month]=5 Then Count([ID] Where([Month]<=5)) ForAll[Month]) Else
If [Month]=6 Then Count([ID] Where([Month]<=6)) ForAll[Month]) Else
If [Month]=7 Then Count([ID] Where([Month]<=7)) ForAll[Month]) Else
If [Month]=8 Then Count([ID] Where([Month]<=8)) ForAll[Month]) Else
If [Month]=9 Then Count([ID] Where([Month]<=9)) ForAll[Month]) Else
If [Month]=10 Then Count([ID] Where([Month]<=10)) ForAll[Month]) Else
If [Month]=11 Then Count([ID] Where([Month]<=11)) ForAll[Month]) Else
Count([ID]) ForAll([Month]) 
Downside, this drains resources. Time to calculate is, I guess as I should expect, huge (some 50 to 60,000 ID's in a year with a lot returning). 

Back to top 


MBLM Principal Member
Joined: 31 Jan 2014
Posts: 210

Posted: Wed Jan 27, 2016 10:28 am Post subject: Re: Running Distinct Count 


Or even better, a certain Rogerio (I guess the same one we know here) replied this on a similar question https://scn.sap.com/thread/3824404 

Back to top 



