KNOWLEDGE BASE

Calculating a Running Count Distinct


Published: 07 Nov 2015
Last Modified Date: 31 May 2017

Question

How to calculate a running (or moving) count distinct of a dimension.

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the attached workbook.

Step 1: Create three calculated fields

  1. Select Analysis > Create calculated field.
  2. Name the calculated field "Moving Active Users" and enter the following formula:
    WINDOW_MAX(MIN([Number of Records]), -2, 0)
  1. Select Analysis > Create calculated field.
  2. Name the calculated field "!CountD Users" and enter the following formula:
    WINDOW_SUM([Moving Active Users])
  1. Select Analysis > Create calculated field.
  2. Name the calculated field "First" and enter the following formula:
    FIRST()

Step 2: Build the view

  1. Right-click-drag Order Date to the columns shelf.
  2. Select Week(Order Date) (with a calendar icon, not a #) and click OK.
  3. Right-click Week(Order Date) on the columns shelf and select Show Missing Values.
  4. Drag Customer to the Detail shelf.
  5. Drag !CountD Users to the rows Shelf.
  6. Drag First to the Filters shelf and click OK.
  7. Right-click on !CountD Users on the rows shelf and select Edit Table Calculation….
  8. Select Customer from the Compute using dropdown.
  9. Right-click on First on the Filters shelf and select Compute using > Customer.
  10. Click OK.

Additional Information

To alter this calculation to a full Running Count (distinct) from the beginning, change the Moving Active Users calculation to rather than a moving count distinct. 
WINDOW_MAX(MIN([Number of Records]), FIRST(), 0)

Important: If you try to reproduce the above steps with a different sample data source, please note that the dimension "Customer" has been mostly replaced by "Customer Name". Therefore the calculated field and the view must take this new name into account. 
Did this article resolve the issue?