Number of unique IDs accumulated

[JonasLang]

Dear Community

I was asked to deliver the number of unique customer IDs year to date split by month, and I cannot myself figure out whether it is possible to calculate this number.

Example:

In January 2019 I had the following customer IDs visiting my website:

AB12

AC13

AD14

This give me 3 unique customers that month.

In February 2019 I had the following customer IDs visiting my website:

AB12

BA21

This give me 2 unique customers that month.

In March 2019 I had the following customer IDs visiting my website:

AB12

AC13

CA31

This give me 3 unique customers that month.

I am then asked to deliver a table that looks like this:

January 2019      3 unique customers

February 2019     4 unique customers

March 2019          5 unique customers

I am using the APPROXIMATE DISTINCT COUNT within Calculated Metrics to find the number of unique customer IDs in total but it does not work with the CUMULATIVE function to split it per month.

Any suggestions on how to solve this?

Thanks in advance!

Best regards

Jonas

[Urs_Boller]

I would use the same function "approx distinct count" as well, it should work even with a month breakdown. maybe you need to count "minus 1" since it will count all "undefined" (users wothout customer id) as a new dimension item (and therefore adding an additional item to the count)

[JonasLang]

Hi Urs

It is not working for me - If I do that my table will look like this:

January 2019      3 unique customers

February 2019     2 unique customers

March 2019         3 unique customers

I want an accumulated table where it is only adding new customers. I have added the time intervals that each month should be covering to explain it a bit further: 

January 2019 (1/1-19 - 31/1-19)        3 unique customers

February 2019 (1/1-19 - 28/2-19)      4 unique customers

March 2019 (1/1-19 - 31/3-19)          5 unique customers

Best regards

Jonas

[Andrey_Osadchuk · Accepted solution]

Hi Jonas,

I think what you may want to do to get the desired result is to update the date ranges as follows:

• Jan 2019
• Jan-Feb 2019
• Jan-Mar 2019

This would help you display cumulative number of visitors to a certain date (month).

[JonasLang]

Hi Andrey

I was hoping that it could be done in a "clever" way. Potentially we want to break it down by week or day as well. It is a lot of custom date ranges to create.

Best regards

Jonas

[Urs_Boller]

I don't know of any fancy way to get de-duplicated cumulative sums of the distinct counts. means you either need to go with the solution described by Andrey Osadchuk​ or forget the idea to have an easy solution... sorry

[kumararajak]

Dear Jona,

This is possible with Excel formula. Let me know if you work with excel and need help to sort this.

Regards,

Kumararaja K

[kumararajak]

The excel work will look like this

[JonasLang]

Thanks kumararajak​!
I will consider doing it in Excel - I was hoping to have the entire dashboard in Analysis Workspace.

Best regards

Jonas

[kumararajak]

here the full function to call