Level 1

0% to

Level 2

Tip /

to gain points, level up, and earn exciting badges like the new
Mission!

View all

## Number of unique IDs accumulated

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile
JonasLang
Level 1

31-07-2019

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

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?

Best regards

Jonas

#### Accepted Solutions (1)

MVP

Likes

538 likes

Total Posts

973 posts

264 solutions
View profile

MVP

Likes

538 likes

Total Posts

973 posts

264 solutions
View profile
MVP

01-08-2019

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).

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile
kumararajak
Level 5

02-08-2019

The excel work will look like this

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile
kumararajak
Level 5

02-08-2019

here the full function to call

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile
JonasLang
Level 1

02-08-2019

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

Best regards

Jonas

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile

kumararajak
Level 5

Likes

119 likes

Total Posts

94 posts

16 solutions
View profile
kumararajak
Level 5

02-08-2019

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

ursboller
MVP

Likes

525 likes

Total Posts

1,020 posts

257 solutions
View profile

ursboller
MVP

Likes

525 likes

Total Posts

1,020 posts

257 solutions
View profile
ursboller
MVP

01-08-2019

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

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile
JonasLang
Level 1

01-08-2019

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

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile

JonasLang
Level 1

Likes

3 likes

Total Posts

6 posts

0 solutions
View profile
JonasLang
Level 1

31-07-2019

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

ursboller
MVP

Likes

525 likes

Total Posts

1,020 posts

257 solutions
View profile

ursboller
MVP

Likes

525 likes

Total Posts

1,020 posts