BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Running Distinct Count


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
MBLM
Principal Member
Principal Member



Joined: 31 Jan 2014

Posts: 210


flag
PostPosted: 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
Principal Member



Joined: 31 Jan 2014

Posts: 210


flag
PostPosted: 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
Principal Member



Joined: 31 Jan 2014

Posts: 210


flag
PostPosted: 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
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0456 seconds using 17 queries. (SQL 0.0209 Parse 0.0008 Other 0.0239)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo