On different occasions, our clients have asked the question, “How can I compare the current quarter to last quarter or next quarter?” So let’s answer it in detail. If we would like to make a comparison of two quarters (or any other date part, i.e. year, month, etc.) and give our users the ability to choose which periods they are comparing, we can use a simple parameter to represent it.

Parameterizing Date Difference in Tableau

Let us assume that we would like to work for now only on quarters, and we have data (perhaps as a forecast) for the future profit. Essentially, we would like to switch between two views: this vs. previous period or this vs. future period.

Note: We will be using the following shortcuts: Y – year, Q – quarter,  W – week, D – day, L – last , C – current, N – next.

We will need to create a parameter as follows:

• Name: Q Control Parameter
• Data type: String
• Allowable values: List
• Values
  • CQ vs. NQ
  • CQ vs. LQ

We will now use those two values we can choose between in our parameter as a CASE statement in a calculation like below:

CASE [Q Control Parameter]
WHEN 'CQ vs LQ' THEN
DATEDIFF('quarter',[Order Date],TODAY()) <= 1
   AND DATEDIFF('quarter',[Order Date],TODAY()) >=0

WHEN 'CQ vs NQ' THEN

DATEDIFF('quarter',[Order Date],TODAY()) <=0
   AND DATEDIFF('quarter',[Order Date],TODAY()) >=-1

END

How Does It Work

DATEDIFF gives us a difference in a specified date part between two dates. In this calculation, we choose to calculate those in quarters between [Order Date] and Today(). If the difference is 1, we are in the previous quarter; 0 would mean no difference; so the current quarter, negative 1, is the next quarter.

Bring your newly made calculation to Color (to color it) or Filter (to show only the relevant), bring your measure (i.e. Profit) to Text, and any additional dimensions (like [Order Date]) to Columns and/or Rows. The following is a visualization in the form of a calendar to show the exact dates we toggle.

Note: At the time of writing this post, we were in Q4 2020.

Above: Parameter set to CQ vs. LQ shows Q4 and Q3

Above: Parameter set to CQ vs. NQ shows Q4 and Q1

Days, Weeks or Years

You can easily adjust the above to show other date parts. For example, if we would like to give our users a possibility to compare weeks, we only have to:

1. change the parameter’s values to reflect the new choice
2. change quarter to week in the calculation

As a parameter, it would look like this:

• Name: W Control Parameter
• Data type: String
• Allowable values: List
• Values
  • CW vs NW
  • CW vs LW

And like this as a calculation:

CASE [W Control Parameter]
WHEN 'CW vs LW' THEN
DATEDIFF('week',[Order Date],TODAY()) <= 1
   AND DATEDIFF('week',[Order Date],TODAY()) >=0

WHEN 'CW vs NW' THEN

DATEDIFF(‘week’,[Order Date],TODAY()) <=0
   AND DATEDIFF(‘week’,[Order Date],TODAY()) >=-1

END

Choose Between Days, Weeks, Quarters, Years

If, additionally, we would like to give our user the ability to choose which date part they can compare, we can enhance our solution with an additional parameter. That, however, will add a bit to the complexity. In such a scenario, we would allow our users to decide whether they compare days, weeks, months, quarters or years.

In addition to the solution above, we would have to add/change:

Add a parameter:

• Name: Date Part Control Parameter
• Data type: String
• Allowable values: List
• Values
  • day
  • week
  • month
  • quarter
  • year

Note: Please write those values in the exact form like above: singular, all lowercase.

Change the control parameter:

• Name: Flexible Control Parameter
• Data type: String
• Allowable values: List
• Values
  • Current vs. Previous
  • Current vs. Next

Change the calculation, adding this parameter in place of the date parts:

CASE [Flexible Control Parameter]
WHEN 'Current vs Previous' THEN
DATEDIFF([Date Part Control Parameter],[Order Date],TODAY()) <= 1
   AND DATEDIFF([Date Part Control Parameter],[Order Date],TODAY()) >=0

WHEN 'Current vs Next' THEN

DATEDIFF([Date Part Control Parameter],[Order Date],TODAY()) <=0
   AND DATEDIFF([Date Part Control Parameter],[Order Date],TODAY()) >=-1

END

The other parameter used previously will not be needed anymore.

And that’s it! Now, we just have to bring it all together in the same fashion we did it above and show both parameters, which we can use to dynamically manipulate our view:

Note: Squares represent data points. Simply drop any measure, i.e. Profit, into Text to replace them.

You can explore and download the complete workbook below. Let us know if you have any questions, and enjoy your Tableau journey!

If you have any questions about this or any other solution, feel free to reach out to me directly via email or LinkedIn. Special thanks to Rowan Bradnum, for your help with this one. I’m sure readers will appreciate it more!