KNOWLEDGE BASE

## Displaying Active Records Over Time

Published: 05 Apr 2017

### 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

##### Option 1: Use Date Scaffolding
Create a table that contains a master list of all possible dates, which will be joined to the original data connection. In this example, that table is called "Master Date List".
1. Connect to the original data source tab in Tableau Desktop
2. Add a connection to the "Master Date List" table
3. Drag the "Master Date List" table to the canvas area
4. In the Join dialog, do the following:
1. Select Left
2. Under Data Source, select [Open Date]
3. Under Master Date List, select [Date]
4. Click the equal sign, and select <=
5. Under Data Source, click Add new join clause, and select 'Create Join Calculation...'
6. Type IFNULL([Close Date],TODAY()) and click OK
7. Under Master Date List, select [Date]
8. Click the equal sign, and select >=
5. Right-click and drag [Date] to the Columns shelf
6. In the Drop Field dialog, select Date (continuous) and click OK
7. Drag [Number of Records] to the Rows Shelf
8. Double-click [Orders Shipped] in the data pane to add the field to the view
##### Option 2: Create Calculations
CLICK TO EXPAND SOLUTION

#### 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.