Date Comparisons in Tableau Made Easy

Data

Date Comparisons in Tableau Made Easy

One of the common problems that clients are seeking to solve is a way to easily compare values from two different periods against each other. They want the ability to compare the same period between two years, a period against an immediately preceding period and current year to date compared to the previous year to date. They will also want to be able to change the length of this period at will, as well.

Problem

  • We need a way to choose easily if we want to use today’s date or a custom date as a starting point and a way to choose this custom date.
  • We need a way to select the length of these periods.
  • We need to select what type of analysis we are going to do (Rolling Period, Year Over Year, Year to Date)

Selecting Today’s Date or a Custom Date

We will create a parameter with two options. This way, we can pass our decision to use today’s date or a custom date to another field:

Tableau: Edit Parameter

 

Next we create another parameter so that if we do opt to use a custom date from the above selection, we can further interact and choose what date we want to use as a starting point:

Tableau: Edit Parameter Custom Date

 

We combine the above two selections into a single calculated field. Using our two parameters and this one calculated field, we can then pass our starting date into the below calculations:

Tableau: DateComparisonToUse Calculation

 

Length of the Periods

One of our goals is to allow our end-user to select what type of time period they want to use and of course the length or how many of these periods to use. We create two parameters to handle this. In the first parameter, by using the singular day, week, month, etc. as our actual value in the left-hand column, we can more readily write calculated fields:

Tableau: Edit Parameter - Period Type

 

Tableau: Edit Parameter - Period Length

 

Now we’ve built the basic framework of the tools, we will be using in actual “hard” calculation. We are able to change the length of our periods, what type of period we want to use and what date we want to base our periods on.

Changing the Type of Analysis to Use

In this section, we will break down each of the period analysis calculations. In order to change our type of calculations, we created another parameter that will allow us to pick the type of period comparison we are attempting to do.

Tableau: Edit Parameter - Analysis Type

 

Year over Year

Tableau: Year over Year

 

WHEN “YoY” THEN

//The first statement sets the conditions for a date to be considered the “selected period.” It will compare every date in our view to the date we’ve chosen as our starting point.

//It must be based on whatever date we’ve chosen and go back a user chosen number of months, weeks, days, etc.

//If the difference between these dates is >=0 and < our Period Length, we consider it to be the “selected period.”

IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0

//The second statement sets the conditions for a date to be considered the “previous period.” It will compare every date in our view to the date we’ve chosen as our starting point.

//It will check with the exact same methodology as the above statement, except it will remove a year from our starting point.

ELSEIF DATEDIFF([Period Type],[Date],DATEADD(‘year’,-1,[DateComparisonToUse])) >=0 AND DATEDIFF([Period Type],[Date],DATEADD(‘year’,-1,[DateComparisonToUse])) < [Period Length] THEN -1

END

Rolling Period

Tableau: Rolling Period

 

WHEN “RP” THEN

//The first statement sets the conditions for a date to be considered the “selected period.” It will check every date to the date we’ve chosen as our starting point.

//It must be based on whatever date we’ve chosen and go back a user chosen number of months, weeks, days, etc.

//If the difference between these dates is >=0 and < our Period Length, we consider it to be the “selected period.”

IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0

//The second statement sets the conditions for a date to be considered the “previous period.” It will compare every date to the date we’ve chosen as our starting point.

//It will be based on whatever date we’ve chosen and it will immediately precede the “selected period” and be the same length.

//If the difference between the dates is > the Period Length but also < two times the length, it will be the “previous period.”

ELSEIF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >= [Period Length] AND DATEDIFF([Period Type],[Date],[DateComparisonToUse]) <2*[Period Length] THEN -1

END

Year to Date

Tableau: Year to Date

 

WHEN “YTD” THEN

//We check every date in our view and see it would come before our selected date in a calendar year

//If it does then we pass it to the case statement below if not we exit out and it is null

IF DATEPART(‘dayofyear’,[Date]) <= DATEPART('dayofyear',[DateComparisonToUse]) THEN

// The case statement determines if the date is in the same year or the previous year. Any other years are null.

CASE YEAR([Date]) – YEAR([DateComparisonToUse])

WHEN 0 THEN 0

WHEN -1 THEN -1

END

END

Putting It All Together

Now we are able to quickly change out comparison periods by using a combination of our various parameters (Analysis Type, Period Type, Period Length, Date Chooser and Custom Date) as these feed into our calculations that do the hard work for us. I’ve aliased 0 as “Selected,” -1 as “Previous” and NULL as “Not Being Used.” To see the final, interactive results of work, check out the Tableau viz below:

More About the Author

Carl Slifer

Analytics Consultant
Digital Nomad: A Year in the Life In the musical Rent, the song “Seasons of Love” asks us how we measure a year: in daylights, in sunsets, in cups of coffee, in inches, ...
How I Went from Army Brat to Digital Nomad I get bored easily. I always have. I’ve had plenty of different jobs, mostly because I became bored with them so fast. My father served ...

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