KNOWLEDGE BASE

Showing Records That Fall Within a Period of Time


Published: 05 Apr 2017
Last Modified Date: 27 Apr 2017

Question

How to show records that fall within a specified period of time. 

Environment

Tableau Desktop

Answer

The below instructions use the attached Open_Contracts.xlsx. 

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])

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

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

Step 4: Build the view 

  1. Drag Date to Rows
  2. On Rows, 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.

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 Label
  8. On Label, 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. 


 

Did this article resolve the issue?