How to Normalize Current Dates and Prior Dates on One Axis in Tableau

Get more from your line graphs with this advanced tactic for equalizing dates

Dates are tricky to work with in Tableau. This tutorial will make it much easier for you to compare a selected date range to the date range immediately preceding it – all on the same axis!

Calculated field referenced that computes the number of days in the selected date range:

DATEDIFF(‘day’,[Start Date],[End Date])+1

Calculated field referenced that determines if the data point is in the selected date range or the date range immediately preceding the selected date range:

IF [Order Date] >= [Start Date] AND [Order Date] <= [End Date] THEN “Current Period”
ELSEIF [Order Date] >= [Start Date] – [Days in Range] AND [Order Date] <= [End Date] – [Days in Range] THEN “Prior Period”
ELSE “Not in Range”
END

Calculated field referenced that normalizes both date ranges so they are displayed on the same axis:

IF [Current Period / Prior Period] = “Prior Period” THEN [Order Date] + [Days in Range]
ELSE [Order Date]
END

Hi. This is Ryan with Playfair Data TV and in this video, I’m going to share one of my favorite advanced tactics in Tableau, which is to normalize dates onto the same axis in Tableau. And what I mean by that is I want to be able to select a date range and see the data points, but then I also want to see the same date range immediately preceding my selection. So I want to know what the period over period performance was.

I’m going to explain why that’s a challenge over here in Tableau Desktop. I made this chart that looks at sales by day of order date. And I’ve got the first week of October selected. So I can see the seven data points but what I’d really like to know is how I did week over week. Those data points aren’t represented on this view from last week. And it’s actually trickier than you might think to get those data points onto this view.

Your first instinct might be, well I could just change the date range and I could back this out to one week ago, which was September 24th. But because this is a continuous date range, all that’s doing is making a longer axis but these lines are not laying right on top of each other. That would really help me analyze the data because I’d be able to see today’s date, let’s say it’s Monday, I’d like to see Monday this week verse Monday last week. That’s exactly what I’m going to show you how to do here in this video.

It involves a few steps. The first ones are pretty easy. We’re going to start by just creating two parameters, a start date and an end date. So I’m going to right click and click create parameter. This first one is going to be called start date. And the data type is date. And I’ll go ahead and set the current value now, since we already know what range we’re looking at, to October 1st. I’ll go ahead and leave the allowable values as all for now. Not too concerned with it at the moment. So I’ll click OK.

I could start a new parameter to create the end date or I can just duplicate what’s already there by right clicking and clicking duplicate. And now I can just edit that second parameter. Call this one my end date. And the end of my range is October 7th. Click OK.

The next thing we need to compute in order to get this to work properly is the number of days in this range. This involves a calculated field. And I’ll just call it days in range. And the formula is DATEDIFF whatever date part we’re working with, in this case is day. Comma. Our start date parameter, comma, our end date parameter, close parentheses. And then if you want to make sure you capture the current day, you need to click plus 1.

So that makes sure that this properly computes the number of days in the range, which is currently 7. October 1st to October 7th. Going to click OK. And we need to make another calculated field that tells Tableau whether or not the data point is in the current period or the prior period. So I’m going to call this current period/prior period.

And the formula this time is if order date, that’s what the name of the date field is in the sample dataset, yours could very well just be called date. But if your date field is greater than or equal to the start date and your date is less than or equal to your end date then that’s your current period. Elseif your date is greater than or equal to the start date minus the number of days in the range.

So what we did there is we’re creating the prior period right now in the second line of the code. It’s going to take that start date minus 7 days, in this case. That’s the beginning of the range for the prior period. And then the top of the range for the prior period is if the order date is less than or equal to the end date minus days in range. Then your prior period. Otherwise, else, this acts as a catch all. We’ll just say you’re not in range. And will eventually want to filter out those data points anyway. So that’s good to do an else there, that will help us filter this in the next step. Then it closes with end. Click OK.

And there is one last calculated field we need to make, which is a date equalizer. This is the step that’s going to take those data points in the prior range and move them forward so that they’re on the same axis. So I’m going to create a calculated field and call this equalized date. And the formula is if the current period or prior period from the very last calculated field we made equals prior period, then we want to take the order date but add the days in the range. And otherwise, we’ll just display the original order date. End. So that’s the whole formula. I’m going to click OK. And we’re now ready to make our chart.

One of the unique things about the parameter approach that we’re using here, is we’re no longer going to use the actual date filter. Instead we’re going to use parameter controls to limit what’s being included on the view. So I want to get rid of our order date field altogether. We see all the data points come back.

I do want to filter this though to only days that are in our range. So I’m going to put our current period/prior period filter onto the view and exclude if they’re not in the range. That gets me September 24th to October 7th. So our current period, October 1st to the 7th, and then the week before it. That’s what’s included here.

I’ll also color the marks by what period they’re in by dragging the current period/prior period calculated field to the color marks card. And here’s exactly what I was talking about. It’s good that we can now see the prior period but these data points would be a lot easier to compare if they were lined up on top of each other.

That’s where this equalized date field comes in. Instead of using day of order date with this continuous axis, I’m going to replace it with my equalized date calculated field. And I’ll choose day again. And now they line up. So this is much easier to consume now. So what this is doing is the first data point for the current period is October 1st but the first data point for the prior period is September 24th. The same day exactly seven days ago, one week ago.

We could confirm this a little bit further by putting the attribute of the original order date onto the detail marks card. And now you can see for the first day of the current period the order date was October 1st. So the day of the equalized date, as well as the order date, the original order date, match. But for the prior period the equalized date is October 1st but the original order date is September 24th.

So now we’ve got this foundation here. There’s one last step with parameters if you want to unlock the flexibility and allow yourself, as well as your end users, to choose the date range. You need to right click on each one of these and click show parameter control. And now regardless of the date that you select, so if I make this end date the entire month of October, this will adjust. It will recompute. So now my current period is October 1st to October 31st. But my prior period is the exact 31 days right before the selection in this range.

And this works great even if you change the date part. So instead of day, if I were to choose weeks, it still equalizes those dates for you. It’s a really effective way to do trends and improve your line graphs. This has been Ryan of Playfair Data TV – thanks for watching!