Cumulative count distinct

[#1]

Hi,

I have a count of distinct customers by month. I need a straight table with month as dimension and # of distinct customers visited till that month.

Basically its a cumulative count distinct function. Please advice how to solve this.

Regards,

Kiran.

[#2]

Are you unable to use the built-in Accumulation functionality? I did a quick test and accumulation seems to work fine on count distinct in a Straight Table.

Otherwise, you may be able to use something like:

count(distinct total if(VisitMonth<=Month and VisitYear<=Year, CustomerID)

You didn't give any information on what fields you were using for the dates and customer, so I guessed.

[#3]

Thanks for your inputs. I tried accumulation but it didnt work. I attached the sample here for more details.

Regards,

Kiran.

[Anonymous]

Remove MonthID as a dimension and the go to the Sort tab and set MonthDescr to sort by expression, MonthID, ascending. Then you should be fine.

[#5]

Did as suggested. The result is the following:

Jan	2
Feb	3
Mar	5
Apr	8
May	12
Jun	16

But the expected result is:

MonthDesc	#Customers
Jan	2
Feb	2
Mar	3
Apr	3
May	4
Jun	4

My expression should be number of distinct customers visited till that month.

Regards,

Kiran.

[Anonymous · Accepted solution]

Ok, that was a little bit trickier.

I put together a solution. Check out the attached example. I added a field in the script called FirstVisit and use that as a flag set to either 1 or 0. Then I just sum up the FirstVisit flag with full accumulation. I had to do a binary load of your file as I don't have the data sources but you can just add the field in your script after sorting the table accordingly.

(Basically I sort the table on MonthID and CustomerID so that when the CustomerID changes I tag that row with the FirstVisit for later use)

[#7]

Wow, thats works great. Thanks a lot Johannes.

[#8]

This is a great solution. But I have a huge datamodel and its will be difficult to do this. Is that a workaround without a datamodel modification?