Interactive Date Comparisons with Tableau Parameters

Data

Interactive Date Comparisons with Tableau Parameters

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!

More About the Author

Igor Garlowski

Analytics Consultant
Local Scheduling and Flow Refreshes with Tableau Prep Running multiple flows every day from your computer and opening them one by one is a method that’s far from perfect. The reason why we ...
Interactive Date Comparisons with Tableau Parameters On different occasions, our clients have asked the question, “How can I compare the current quarter to last quarter or next quarter?” ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072