How to Compare the Last Two Full Date Parts in Tableau

Automatically Compare Full Days, Weeks, Months, Quarters, or Years

Learn a calculated field that (1) looks at today’s date, (2) automatically identifies the last two full date parts, and (3) classifies dates as Full Current or Full Prior so you can do a period over period comparison.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to be sharing a calculation that automatically keeps only the last two complete date parts on a line graph in your view. This is going to be very handy for you to help avoid unnecessary panic. That’s the reason we’re using this.

To help illustrate, I’ve created this chart over here with the Sample Superstore dataset that looks at Sales by continuous week of Order Date. And because the Sample Superstore dataset currently only runs through the year 2018, I’ve just created this fake scenario that filtered this at 11/14/2018. If I was presenting this analysis to a stakeholder, they’re immediately going to be drawn to this last data point. They’re going to say, what happened compared to last week? Why are we down so far?

Well, let’s take a closer look at the calendar. Back on 11/14/2018, well, that’s a Wednesday. So we’re only comparing– Tableau weeks start on Sunday– so we’re only comparing 1, 2, 3, 4 days for this last data point compared to 7 days in all of the other weeks.

So what this video is going to explain how to do is to just keep the last two full comparisons, so we’ve got an apples-to-apples comparison. We avoid this unnecessary panic. All you need to do to create this is one calculated field. I’ve already gone ahead and created it because you’re not meant to just sit here and watch me type. But I will explain what this calculated field is doing.

It’s saying, first of all, if the Order Date, or whatever date field you’re using in your dataset, is less than the maximum date– and I had to hard code that for this example because this– I’m going to show you later on how to set this up to be automated and based on today’s date. But for the purposes of this example, I’ve hard-coded 11/14 as my maximum date. So let’s pretend that that’s today.

So this first line is saying, first of all, the date has to be less than today. It then classifies each week in the dataset as either the Full Current or the Full Prior period. To compute the Full Current, we’re just doing a DATEADD here with a week date part. Remember, anytime you see blue in a Tableau calculated field, you can click on it to get a definition for what’s happening as well as how that is used in the syntax.

But what this is doing is looking at today’s date. It already has to be less than today’s date. It’s then taking that date minus one week. So that is the last full date part is what that’s creating. And therefore, I have classified it as Full Current.

The next line is something very similar. But instead of a minus 1, I do a minus 2. That gets me two full weeks ago. I will share this calculated field in the related content below. Just to point out what you will need when you do this on your own, you will just replace the hard-coded date with this function– TODAY, open parenthesis, closed parenthesis.

And I’m going to get us back to the hard-coded date and click OK so that I can show you how this works. I’m going to click OK. Also, instead of a line graph, I’m just going to get rid of my measure and convert this to circles so we can see how these are being classified. Remember, this is the current week in our scenario. This is the last full week, and this is two full weeks ago. Everything else is not within those last two periods.

If I put this onto the Color Marks Card, as you can see, the current week is not in the range. So it’s color-coded null. The last full week is colored orange and represents the Full Current period. The two weeks ago would be our Full Prior period, is what I’m calling it. And that’s color-coded red. Everything else is not in the range.

Now that we have that calculation, there’s a lot of things we can do with this. One of my favorite applications of this is to create comparisons. What I’d encourage you to check out is the video How to Create a Comparison Callout in Tableau. It’s one of my favorite dashboard elements. It’s over on the Dashboards track.

But now that we’ve got this, we can make comparisons out of all of our measures. We can say that ‘if this calculated field equals Full Current, then Sales’ divided by ‘if this field equals Full Prior, then Sales.’ And you’re just creating a week-over-week comparison.

I also want to point out that I used week for this example, but this works with any date part. You could put day in here, and it would be comparing the last full day to the day before it. You could put month in here. It would be comparing the last full month to the month before it. You could also put quarters or years in here. It works for all of those. You can also parameterize those dates to make them even more flexible so that your end user can just use a parameter control to update what’s classifying the Full Current versus Full Prior date period.

And one last way this can be used– this is where this really comes to life. You can use this field as a filter. So to help illustrate, let’s make this a more real-life scenario. I’m going to click OK to close this. We’ll say we’re looking at days instead of weeks. So notice, we still have the correct color-coding. The last full period has seven circles colored orange. Two periods ago has seven circles colored red.

If I were back to looking at this as a line graph, we would see something like this. Because I’ve got a continuous axis now, it’s connecting those null values. But that’s actually what I want to show you anyway. Really our concern with this view is the week-over-week comparison, so I’m actually going to get rid of the nulls altogether by just dragging my Full Current versus Full Prior field onto the Filters Shelf, deselecting null, click OK, and now we’ve got a automated, full week, period-over-period comparison.

There’s one more advanced video here at Playfair Data TV that’s related to this, which is called How to Normalize Your Dates. There’s one other step if you wanted to move those red lines up on the x-axis so that they line up with all the orange data points, I encourage you to check out that video.

But for now, this has been Ryan with Playfair Data TV – thanks for watching!