KNOWLEDGE BASE

Comparing Data from the Same Number of Days in Two Periods


Published: 26 Sep 2016
Last Modified Date: 26 Oct 2016

Question

How to compare the same number of days worth of data between two periods.

For example: using the Sample - Superstore data source, we will assume that today is the January 6, 2016. Since the month of January is not yet finished, we will want to compare the Sales results between this month and the first days of the previous month (December). The first days of data are the first business days, not days 1 to 6.

Environment

Tableau Desktop

Answer

Step 1: Create a field containing a day in the current period

  1. Select Analysis > Create Calculated Field
  2. In the Create Calculated Field dialog box, do the following and click OK:
  • Name the field. In the example, this field is called "!day in current period"
  • Use the date() function to enter a day in the most recent period or use the today() function for a dynamic date:
    DATE("01-06-2016")
    
    • If you are based in the UK, change the date here to DD-MM-YYYY
  • TODAY()

Step 2:  Find how many days of data are in the current month

1. Select Analysis > Create Calculated Field
2. In the Create Calculated Field dialog box, do the following and click OK:
  • Name the field. In the example, this field is called "!count of days in current period"
  • Create a calculation similar to the following:
{FIXED:COUNTD(IF DATETRUNC('month', [Ship Date])=DATETRUNC('month', [!day in current period]) THEN DAY([Ship Date]) END)}

Step 3: Find the rank of days in their months

1. Select Analysis > Create Calculated Field
2. In the Create Calculated Field dialog box, do the following and click OK:
  • Name the field.  In the example, this field is called "!rank of day in period"
  • Create a calculation similar to the following: 
    RANK_MODIFIED(MAX([Ship Date]),'asc')
    
  • Click Default Table Calculation at the bottom of the dialog box
  • For Compute using, select 'Ship Date'
  • For At the level, select 'Day of Ship Date'
  • For Restarting every, select 'Month of Ship Date'
  • Select OK on both boxes to return to the main view

Step 4: Create the filter

1. Select Analysis > Create Calculated Field
2. In the Create Calculated Field dialog box, do the following and click OK:
  • Name the field.  In the example, this field is called "!filter for equal number of days"
  • Create a calculation similar to the following:
IF [!rank of day in period] <= MAX([!count of days in current period])
THEN 'keep'
ELSE 'remove'
END

Step 4: Build the view

  1. On a new worksheet, place [Ship Date] on Columns and click the + on the pill to expand the date out to days
  2. Place [Sales] on Rows
  3. Place [!filter for equal number of days] on the Filters shelf and select 'keep'

Additional Information

  • "!rank of day in period" does not need to be in the view anywhere, as long as the view allows it to be calculated.
  • Replace [Ship Date] with the desired dimension
  • The attached workbook has an extract of Superstore data containing only weekdays in December 2015 and part of January 2016
  • The example uses a field called '!day in current period' instead of today() because the last day in the data source is January 6th, 2016. 

To view these steps in action, see the video below: 


 
Did this article resolve the issue?