KNOWLEDGE BASE

Show Records That Fall Within a Period of Time


Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 16 Aug 2016

In your analysis, you might need to identify the total number of records that fall within a specified period of time. The following examples describe two different methods that you can use to do this.

  • Method one uses a combination of data blending and calculated fields to identify the records.

  • Method two uses a combination of table calculation functions to identify the records.

Method 1: Data blending and calculated fields

In this example, a retailer wants to identify all products that have an active status within a specific month. Products are assigned an active status when they are ordered but not yet shipped.

Step 1: Connect to your data source

Note: This example copies and renames the data source to illustrate the fields that are used for the data calculations. You don't have to rename the data source.

  1. Open Tableau Desktop and connect to your data source. This example uses the Sample - Superstore data source.

  2. In the Data pane, right-click Sample - Superstore, select Rename and enter the name Ordered.

  3. In the Data pane, right-click the Ordered data source, and select Duplicate.

  4. Right-click the Ordered (copy) data source, select Rename and enter the name Shipped.

Step 2: Create field calculations

This step uses calculated fields to blend data. You create a new date dimension to represent the order date and the ship date. Then Tableau can blend the two dates so that you can calculate the difference between them.

  1. Select the Ordered data source.

  2. Select Analysis > Create Calculated Field.

  3. In the dialog box, do the following and then click OK:

    • Name the calculated field. In this example, the calculated field is named Date.

    • In the formula pane, enter [Order Date]

  4. Select the Shipped data source.

  5. Select Analysis > Create Calculated Field.

  6. In the dialog box, do the following and then click OK:

    • Name the calculated field using the same name that you used for the calculated field in Step 3 above. In this example, the calculated field is named Date.

    • In the formula pane, enter [Ship Date]

Step 3: Build the view for the ordered products

  1. Select the Ordered data source and right-click + drag (Option-drag on the Mac) the new Date dimension to the Columns shelf.

  2. In the Drop Field dialog box, select the continuous option MONTH (Date), and then click OK.

  3. Right-click + drag (Option-drag on the Mac) Order ID to the Rows shelf.

  4. In the Drop Field dialog box, select CNTD (order ID) to use a distinct count of orders by order ID as the axis for the rows.

  5. Double-click the sheet tab, and enter a name for the sheet. In this example, the sheet name is Ordered products.

Step 4: Build the view for the shipped products

Note: So that Tableau can blend the dates, use the first Date calculated field that you created in the Ordered data source to create the view.

  1. Add a new sheet.

  2. Select the Ordered data source and right-click + drag (Option-drag on the Mac) the Date dimension to the Columns shelf.

  3. In the Drop Field dialog box, select the continuous option MONTH (Date), and click OK.

  4. Select the Shipped data source, and right-click + drag (Option-drag on the Mac) Order ID to the Rows shelf.

  5. In the Drop Field dialog box, select CNTD (order ID) to use a distinct count of orders by order ID as the axis for the rows.

  6. Double-click the sheet tab, and enter a name for the sheet. In this example, the sheet name is Shipped products.

Step 5: Build the view for the difference between ordered and shipped products

  1. Add a new sheet.

  2. Select the Ordered data source, and right-click + drag (Option-drag on the Mac) Date to the Columns shelf.

  3. In the Drop Field dialog box, select the continuous option MONTH (Date), and click OK.

  4. Create a calculated field to calculate the difference between the ordered and shipped products. Do the following and then click OK:

    • Select Analysis > Create Calculated Field.

    • Name the calculated field. In this example, the calculated field is named Delta.

    • In the formula pane, enter CountD([Order ID])-IFNULL(COUNTD([Shipped].[Order ID]), 0)

  5. Create a second calculated field to calculate the sum of the active products. Do the following and then click OK:

    • Select Analysis > Create Calculated Field.

    • Name the calculated field. In this example, the calculated field is named Number of Active Products.

    • In the formula pane, enter RUNNING_SUM([Delta])

  6. Drag the Number of Active Products measure to the Rows shelf.

  7. Double-click the sheet tab, and enter a name for the sheet. In this example, the sheet name is Active Over Time.

The following dashboard shows an example of how all three sheets might look:

Method 2: Table calculation functions

Step 1: Define the beginning of the period

The calculations that you create in this step identify a start date at the week level, which is based on the earliest date value displayed in the current view. Then, an increment of one is added for each remaining week in the data.

  1. Open Tableau Desktop and connect to your data. This example uses the attached Open Contracts.xslx data source.

  2. Select Analysis > Create Calculated Field.

  3. In the dialog box, do the following, and then click OK:

    • Name the calculated field. In this example, the calculated field is named Start Date.

    • In the formula, enter a calculation similar to the following: DATETRUNC('week',WINDOW_MIN(MIN([Date])))

  4. Select Analysis > Create Calculated Field.

  5. In the dialog box, do the following, and then click OK:

    • Name the calculated field. In this example, the calculated field is named Running Date.

    • In the formula, enter a calculation similar to the following: DATEADD('week',(INDEX() - 1),[Start Date])

Step 2: Define the duration of the period

The calculation that you create in this step identifies the records between the start date and the latest end date value displayed in the current view.

  1. Select Analysis > Create Calculated Field.

  2. In the dialog box, do the following, and then click OK:

    • Name the calculated field. In this example, the calculated field is named isActive.

    • In the formula, enter a calculation similar to the following:

      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

The calculation that you create in this step counts the number of records that were identified in the previous step.

  1. Select Analysis > Create Calculated Field.

  2. In the dialog box, do the following, and then click OK:

    • Name the calculated field. In this example, the calculated field is named Number of Active Contracts.

    • In the formula, enter a calculation similar to the following: IF INDEX() = 1 THEN WINDOW_SUM([isActive])END

Step 4: Build the initial view

  1. Drag Date to the Rows shelf.

  2. On Rows, right-click (control-click on a Mac) Date, and then select Week Number.

  3. On Rows, right-click Date again, and then select Discrete.

  4. Right-click any week header, and then select Show Missing Values.

  5. Drag Client to Detail.

Step 5: Configure table calculations for the new fields

The following procedures specify the order in which to apply the calculations that you created in the steps above. For more information, see Addressing and Partitioning in Tableau Desktop Help.

Start Date field

  1. Drag Start Date to Detail.

  2. On Detail, right-click (control-click on a Mac) Start Date, and select Edit Table Calculation.

    • In the Compute using drop-down list, select Advanced.

    • In the Advanced dialog box, move Week of Date and Client to Addressing, in that order.

  3. Click OK twice.

Running Date field

  1. Drag Running Date to Detail.

  2. On Detail, right-click Running Date, and select Edit Table Calculation.

  3. In the Compute using drop-down list, select Advanced.

  4. In the Advanced dialog box, move Client and Week of Date to Addressing, in that order.

  5. Click OK.

  6. In the Restarting every drop-down list, select Client, and then click OK.

isActive field

  1. Drag isActive to Detail.

  2. On Detail, right-click (control-click on a Mac) isActive, and then select Compute using > Date.

Number of Active Contracts field

  1. Drag Number of Active Contracts to Label.

  2. On Label, right-click (control-click on a Mac) Number of Active Contracts, and then select Edit Table Calculation.

    • In the Compute using drop-down list, select Advanced.

    • In the Advanced dialog box, move Client to Addressing.

  3. Click OK twice.

Here is an example of what the final view might look like.

 

Alternate Search Terms:table calc, table calculation, active items, start date, end date
Did this article resolve the issue?