3 Replies Latest reply on Apr 8, 2018 9:54 PM by Tushar More

# How to get Non-Overlapping Distinct Count by Month from Total Distinct count?

I am fairly new to Tableau. I went through various online forums but was unable to find a right approach to my question here.

I want to track down only the NEW users that are adding by MONTH.

I was able to count the TOTAL number of unique users by `COUNTD()` function. However, I am unable to calculate it by monthly.
In other words, how will I be able to SPLIT this TOTAL into monthly distinct users?

For eg. There are 100 unique users recorded for a 3 month period and when I graph this data by month, tableau tends to calculate the distinct users for that month which overlaps with the other months distinct users. So that means, when I add these individual distinct users count, it is exceeding 100.
My goal is to get the sum of distinct users per month equal to the total distinct users of 100 (not overlapping with other months).

I tried following approaches:

Lets say I want the distinct count of dimension userID by monthly Timestamp and I have tried the following expressions:

NOTE: I do not have separate month column and I was just dragging the Timestamp to columns shelf and drilled it into month view. So according to this, how would I write that expression?

My timestamp data is in this format: "`19/01/2018 11:41:37 AM`"

`{exclude [userID]: COUNTD([userID])}`

`WINDOW_SUM(COUNTD(userID))`

`{FIXED DATE(MONTH([Timestamp])): COUNTD([userID])}`

and none of them worked!

Any help is much appreciated. Thanks.

• ###### 1. Re: How to get Non-Overlapping Distinct Count by Month from Total Distinct count?

Hi Hema,

Here is my approach.

Create a calculated field.

Place this to filter and select true.

Let me know if this helps.

~Tushar

• ###### 2. Re: How to get Non-Overlapping Distinct Count by Month from Total Distinct count?

Thanks Tushar! This worked!

Any brief explanation on why did you use the MIN() function instead of COUNTD() function in the FIXED LOD expression would be much helpful to me!

• ###### 3. Re: How to get Non-Overlapping Distinct Count by Month from Total Distinct count?

Hi Hema,

As per your requirement, you don't just want to count distinct users in each month; you want to display new users in each month. The formula calculates first date of each user and compares with the actual dates.