KNOWLEDGE BASE

Displaying Active Records Over Time


Published: 05 Apr 2017
Last Modified Date: 19 Apr 2018

Question

If each record has an open and close date, how to display active records over time

For example, using the attached data set containing start/end dates for each client, how many clients were active on August 4, 2013?

Environment

Tableau Desktop

Answer

CLICK TO EXPAND STEPS

Step 1: Define the beginning of the period 

  1. Select Analysis > Create Calculated Field
  2. Name the field Start Date, enter the following calculation, and click OK: 
    DATETRUNC('week',WINDOW_MIN(MIN([Date])))
  3. Select Analysis > Create Calculated Field.
  4. Name the field Running Date, enter the following calculation, then click OK:
    DATEADD('week',(INDEX() - 1),[Start Date])
CLICK TO EXPAND STEPS

Step 2: Define the duration of the period 

  1. Select Analysis > Create Calculated Field.
  2. Name the field isActive, enter the following formula, then click OK: 

    IF [Running Date] >= WINDOW_MAX(MAX([Date]))
    AND [Running Date] <= WINDOW_MAX(MAX([Closed Date]))
    THEN 1
    ELSE 0
    END

CLICK TO EXPAND STEPS

Step 3: Determine the active items

  1. Select Analysis > Create Calculated Field.
  2. Name the field Number of Active Contracts, enter the following formula, then click OK: 
    IF INDEX() = 1 THEN WINDOW_SUM([isActive])END
CLICK TO EXPAND STEPS

Step 4: Build the view 

  1. Drag Date to Columns
  2. On Columns, right-click Date and select Week Number.
  3. Right-click Date again and select Discrete.
  4. Right-click any week header, and select Show Missing Values.
  5. Drag Client to Detail.
CLICK TO EXPAND STEPS

Step 5: Configure table calculations and complete the view 

  1. Drag Start Date to Detail
  2. On Detail, right-click Start Date and select Edit Table Calculation
    • Under Compute Using, select Specific Dimensions.
    • Check the boxes next to Week of Date and Client, then close the Table Calculation dialog box. 
  3. Drag Running Date to Detail
  4. On Detail, right-click Running Date and select Edit Table Calculation.
    • Under Compute Using, select Specific Dimensions
    • Move Client above Week of Date.
    • Check the boxes next to Client and Week of Date
    • Under Restarting Every, select Client, then close the Table Calculation dialog box. 
  5. Drag isActive to Detail
  6. On Detail, right-click isActive, then select Compute Using > Date
  7. Drag Number of Active Contracts to Rows
  8. On Rows, right-click Number of Active Contracts, and select Edit Table Calculation
    Check the box next to Client, uncheck Week of Date if it is checked, then close the Table Calculation dialog box. 

*Optional: To include records that have no closed date, use IFNULL() to convert the NULL values in the [Closed Date] field to some arbitrary future date:
IFNULL([Closed Date],#1/1/2999#)

To view the steps showed in the below video, please expand the above section.
Note: the video has no sound.
 

Additional Information

The above instructions use the attached Open_Contracts.xlsx data set
Did this article resolve the issue?