Search

Current Period Vs Previous Period Comparison in Tableau


Current period vs previous period comparison in Tableau isn't as straightforward as in Excel – however, it is much more powerful, because it allows the user to slice and dice, in order to see and understand the differences between various periods. Here is the set of controls that I want to offer to the users:

These controls take any day, month, quarter or year, and compare it to the previous period, or same period the previous year. The user can pick any metric and any period they like.

Let me show you how to quickly build this set of controls with Superstore data using only 4 simple parameters and 3 simple calculations!

Step 1. Configure parameters

  • Start date

  • Compare by

  • Compare to

  • Metric

Let's start with the start date picker. Right click on the side bar, and select 'Create Parameter'.

Use the following settings:

Data type: date

Display format: automatic

Allowable values: range

Check to set minimum and maximum range of values

Step size: 1 Day

Name the parameter 'Start Date' or 'Pick a Date' and click OK.

To show this parameter, right click on it and select 'Show Parameter Control'. This lets your users set the start date.

Now, let's add the 'Compare by' parameter.

Right click on the side bar and select 'Create Parameter'.

Use the following settings:

Data type: string

Allowable values: list

Type in a list of values: day, month, quarter, year.

Name this parameter 'Compare by' and click OK.

Show this parameter by right-clicking on it and selecting 'Show Parameter Control'.

This parameter will save us a lot of time. We will use it in our calculations to let the user drill down through the data.

Next, create the 'Compare to' parameter.

Right click on the side bar and select 'Create Parameter'.

Use the following settings:

Data type: integer

Allowable values: list

Type in a list of values: value: 1,2; display as: 'Prev Period', 'Last Year'.

Name this parameter 'Compare to' and click OK.

Show this parameter by right-clicking on it and selecting 'Show Parameter Control'.

This parameter will allow users to select the period they want to compare to. We will use this parameter in our calculations later.

Finally, create the 'Metric' parameter.

Right click on the side bar and select 'Create Parameter'.

Use the following settings:

Date type: string

Allowable values: list

List of values: type in measures that you want your users to compare. In my case, these are 'Profit', 'Quantity', and 'Sales'.

Name this parameter 'Metric' and click OK.

Show this parameter by right-clicking on it and selecting 'Show Parameter Control'. This parameter lets users quickly switch between different metrics, and essentially eliminates the need to create a dashboard for each metric.

We now have all the controls that we need, but none of them work yet. Let's use the parameters in our calculations to tie the controls in with the data.

Step 2. Create calculations

  • Select a metric

  • Current Period

  • Comparison Period

First, let's tie together our Metric parameter and the metrics that the user can select.

Right click on the side bar and select 'Create Calculated Field'. Use a simple nested IIF statement to let Tableau know when to use what data.

Here is my calculation for the superstore data:

IIF([Metric]="profit",[Profit], IIF([Metric]="quantity",[Quantity],IIF([Metric]="sales",[Sales],0)))

Name this calculation 'Select a metric' and click OK.

Now let's create the 'Current Period' calculation.

Right click on the side bar and select 'Create Calculated Field'.

What we want here is to have Tableau take the user’s input (Start Date), calculate the duration of the period that the user wants to use (Compare by), and calculate the value for the metric that the user has selected (Metric).

Here is the calculation for the Superstore data:

IF

(DATETRUNC([Compare by], [Order Date]) =

DATETRUNC([Compare by], [Start Date])

AND

DATETRUNC('day', [Order Date]) <=

DATETRUNC('day', [Start Date]))

THEN

[Select a metric]

END

Now let's create the 'Comparison Period' calculation.

Right click on the side bar and select 'Create Calculated Field'.

This calculation is similar to the previous one, with one main exception: it finds the comparison period for the previous year by using the DATEADD calculation.

Here is my calculation for the Superstore data:

IF

DATETRUNC([Compare by], [Order Date]) =

DATETRUNC([Compare by], DATEADD([Compare by], -IIF([Compare to]=1,1,IIF([Compare to]=2 AND [Compare by]="day",365,IIF([Compare to]=2 AND [Compare by]="month",12,IIF([Compare to]=2 AND [Compare by]="quarter",4,IIF([Compare to]=2 AND [Compare by]="year",2,0))))),[Start Date]))

AND

DATETRUNC('day', [Order Date]) <=

DATETRUNC('day', DATEADD([Compare by], -IIF([Compare to]=1,1,IIF([Compare to]=2 AND [Compare by]="day",365,IIF([Compare to]=2 AND [Compare by]="month",12,IIF([Compare to]=2 AND [Compare by]="quarter",4,IIF([Compare to]=2 AND [Compare by]="year",2,0))))),[Start Date]))

THEN

[Select a metric]

END

Optional, but frequently required, are calculations for ‘Difference’ and ‘Percent Difference’ – so I'll include these here too.

Difference:

SUM([Current Period])-SUM([Comparison Period])

Percent Difference:

(SUM([Current Period])-SUM([Comparison Period]))/SUM([Comparison Period])

Now we are ready to finish up by building a simple dashboard for the user.

Drag and drop a Dimension on the rows – for example, 'Region'.

Drag and drop our calculations on the Text Marks Card. Tableau will automatically aggregate our calculations.

Remember to change the format of the 'Percent Difference' to percentage by right-clicking on the AGG (Percent Difference) pill and selecting 'Format...'.

With only 4 simple parameters and 3 simple calculations, we’ve created a current period vs previous period comparison in Tableau, with flexible and powerful controls that allow the user to drill down and explore data across various periods and metrics.

Thank you for reading this article. Subscribe below to receive more data visualization tips and tricks like this one.

#CurrentPeriodVsPreviousPeriodComparisoninTa #Tableauparameters

Privacy Policy

 

© 2016-2021 by Olga Tsubiks