TABLEAU Calculating a Running DISTINCT COUNT on usernames for last 3 months

2
votes

Issue:

Need to show RUNNING DISTINCT users per 3-month interval^^. (See goal table as reference). However, “COUNTD” does not help even after table calculation or “WINDOW_COUNT” or “WINDOW_SUM” function.

^^RUNNING DISTINCT user means DISTINCT users in a period of time (Jan - Mar, Feb – Apr, etc.). The COUNTD option only COUNT DISTINCT users in a window. This process should go over 3-month window to find the DISTINCT users.

Original Table
Date Username 1/1/2016 A 1/1/2016 B 1/2/2016 C 2/1/2016 A 2/1/2016 B 2/2/2016 B 3/1/2016 B 3/1/2016 C 3/2/2016 D 4/1/2016 A 4/1/2016 C 4/2/2016 D 4/3/2016 F 5/1/2016 D 5/2/2016 F 6/1/2016 D 6/2/2016 F 6/3/2016 G 6/4/2016 H

Goal Table enter image description here

Tried Methods:

Step-by-step: Tried to distribute the problem into steps, but due to columnar nature of tableau, I cannot successfully run COUNT or SUM (any aggregate command) on the LAST STEP of the solution.

STEP 0 Raw Data

This tables show the structure Data, as it is in the original table.

STEP 1 COUNT usernames by MONTH

The table show the count of users by month. You will notice because user B had 2 entries he is counted twice. In the next step we use DISTINCT COUNT to fix this issue.

STEP 2 DISTINCT COUNT by MONTH

Now we can see who all were present in a month, next step would be to see running DISTINCT COUNT by MONTH for 3 months

STEP 3 RUNNING DISTINCT COUNT for 3 months

Now we can see the SUM of DISTINCT COUNT of usernames for running 3 months. If you turn the MONTH INTERVAL to 1 from 3, you can see STEP 2 table.

LAST STEP Issue Step

GOAL: Need the GRAND TOTAL to be the SUM of MONTH column.

Request:

I want to calculate the SUM of '1' by MONTH. However, I am using WINDOW function and aggregating the data that gave me an Error.

WHAT I NEED

Jan Feb March April May Jun 3 3 4 5 5 6

WHAT I GOT

Jan Feb March April May Jun 1 1 1 1 1 1

My Output after tried methods: Attached twbx file. DISTINCT_count_running_v1

HELP taken:

2
tableau-api
Your problem was solved in this issue? If yes, please post the answer. – kepy97

2 Answers

0
votes

The way I approached the problem was identifying the minimum login date for each user and then using that date to count the distinct number of users. For example, I have data in this format. I created a calculated field called Min User Login Date as { FIXED [User]:MIN([Date])} and then did a CNTD(USER) on Min User Login Date to get the unique user count by date. If you want running total, then you can do quick table calculation on Running Total on CNTD(USER) field.

-1
votes

You need to put Month(date) and count(username) in the columns then you will get result what you expect. See screen below enter image description here