×

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.

• 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!

### The Latest

• ##### InterWorks Blog Roundup – July 2024
Easily move to Tableau Cloud with our Tableau Cloud Migration checklist.

### KeepWatch by InterWorks

Whether you need support for one platform or many, our technical experts have you covered.

## Igor Garlowski

Analytics Consultant
Use Cases with KPIs: New Possibilities with Tableau Map Layers Layers have brought many new possibilities to Tableau. Previously, my colleague Rowan described in detail how to use layers with maps, ...
Viz for Social Good: Build up Nepal Visualizations can serve different purposes. Whether it’s business reporting, sharing production status or providing a research ...

See more from this author →

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

×