  Not endorsed by or affiliated with SAP  Follow BOB on Twitter! (Opens a new window) Index | About Sponsors | Contact Us | Calendar | FAQ | Rules and Guidelines | Privacy Policy | Search 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 Select a search Explain These Choices... --------------------Recent Topics (All Forums) Unanswered Posts
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] 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). 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 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 Register or Login to Post   Page 1 of 1 All times are GMT - 5 Hours

 Not endorsed by or affiliated with SAP Powered by phpBB © phpBB Group Generated in 0.0279 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0241) 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 