5 Replies Latest reply on Apr 1, 2017 9:03 AM by Daniel Berlin

# Running count distinct

Hi All,

I have data of user IDs of users who have opened emails sent to them with particular dates. I have created a chart of running total of count of distinct users who opened the emails by date.

I want to create a graph that shows running distinct count which count distinct users as mentioned below:

January - unique count of users in January

February -  unique count of users for the period January to February

March - unique count of users for the period January to March

April - unique count of users for the period January to April

Can you help me to get the desired result?

• ###### 1. Re: Running count distinct

Hey Ubair,

You will want to drag the users onto the viz, and calculated with COUNTD(). Then, add a Quick Table Calculation of Running Total, and that should get you the desired result. See screenshot below:

Walt

• ###### 2. Re: Running count distinct

Hello Walt,

Thanks for the information. Running total will provide the sum of unique logins in previous months + unique logins in concerned month.

For example running total for February will be the sum of count distinct of January and count distinct of February. But there is a possibility that the person who login in January, login again in February. So for February we need count distinct of the period Jan - Feb.

Hope this makes sense.

Regards,

Ubair Usmani

• ###### 3. Re: Running count distinct

Hey Ubair,

My apologies for the misunderstanding! I see what you're trying to

accomplish. Not being at my computer, I'll attempt to get you going in the

right direction.

First, I would do an LOD calc to determine the initial login month:

{FIXED  : MIN()}

Then you would add this field to the viz, then do a COUNTD(), then

add a Quick Calculation of running total.

I will try this when I get back to the office to make sure it works.

Walt

EDIT: I checked when I got to the office and this solution worked.

• ###### 4. Re: Running count distinct

You can compare their first activity date with current month and count it as 1 otherwise 0 and then do a running sum over it.

E.g. SUM(IF {FIXED [customer name] : min(Date)}==Date then 1 else 0)

If you drag this field into the view that has months as dimensionality and e.g. if customer made first purchase on january 2nd but then bought more on january 5th  and march 2nd etc. except january 2nd they will all count as 0. so when you do a sum, they won't have any effect. It will count as 1 in January and 0 in all other months.

Because of this when you do a running sum, it won't double count existing customers.

• ###### 5. Re: Running count distinct

Hi Ubair,

I have a workbook on Tableau Public that illustrates how to use the solution Walt proposed.  The captions and annotations in the workbook explain the ideas behind this method.

https://public.tableau.com/views/RunningCountD/Story1?:embed=y&:display_count=yes