How to Compare the Last Two Partial Date Parts in Tableau

Automatically Compare the Last Two Partial Weeks or Months

Ryan shares the three calculated fields required to automatically compare the same number of days from the current partial period to the same number of days during the previous partial period so you always have an apples to apples comparison.

1. Calculated field that isolates the latest date in the dataset:

{MAX([Order Date])}

2. Calculated field computes the number of days in the current partial date period:

DATEDIFF(‘day’,DATETRUNC(‘week’,[Max Date]),[Max Date])

3. Calculated field that automatically classifies the last partial date part (i.e. week) as “Partial Current” and the partial date part before it as “Partial Prior”:

IF [Order Date] <= [Max Date]
AND [Order Date] >= DATETRUNC(‘week’, [Max Date]) THEN “Partial Current”
ELSEIF [Order Date] >= DATEADD(‘week’, -1, DATETRUNC(‘week’, [Max Date]))
AND [Order Date] <= DATEADD(‘week’, -1, DATETRUNC(‘week’, [Max Date])) + [Days in Range] THEN “Partial Prior”
END

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you how to automatically compare the last two partial date parts in your dataset. This is another tactic for avoiding unnecessary panic.

Let’s say that you get to work on a Thursday morning, and your company updates their data once per day overnight. So in this scenario we’ve got data through Wednesday. If we were to do a week over week comparison, we would have the four days this week– because Tableau dates by default start on Sundays– so we’d have Sunday, Monday, Tuesday and Wednesday, compared to seven days last week. Well of course, more often than not, the current week, since it’s partial, is going to show a period over period decline, only because it hasn’t had the full seven days to gain its performance.

What this video is going to show you how to do, I’m going to share a few calculations with you that allow you to parse out an apples to apples comparison. So if you’ve got Sunday through Wednesday this week, it will automatically determine what was Sunday through Wednesday last week. So the same days, week over week, you’ll have a better sense of how you’re performing.

This is similar to the last video here on the advanced track that showed you how to look at the last two full date parts, but there’s two extra calculations required. Because the dates can be arranged anything from one day to one day to January 1 through December 30– 364 days– we need to compute the number of days in that apples to apples comparison. And because of that, there are two extra formulas that we need. Over here in Tableau Desktop, I’m going to show you those two formulas.

The first is a level of detail calculation that looks at your dataset and determines what is the highest date in that data source. For the purposes of illustration, I should note that I have filtered the Sample Superstore dataset to November 14, 2018. It currently only runs as of this recording through the year 2018, so I wanted to create a use case where I’m showing you a partial period. So I went ahead and filtered it. The highest date in this dataset is now 11/14/2018. And that’s the first formula you need. It’s as simple as that.

The second formula will compute the number of days in the partial date range. So what this one does is, it looks at– it does a DATEDIFF function. And remember, anytime you see blue in a Tableau calculated field, you can open this little data dictionary and click on what’s blue. It will look it up, give you a definition, as well as how it is used in the syntax. But essentially what this is doing is it’s computing the number of days between the current week truncated at the weekly level. So if it’s November 14, it goes back to the Sunday before November 14. It determines the number of days between that Sunday and the highest date in your data set. So in this case, that would be four days.

The third formula that you need is very similar to when you’re looking at full date parts. What we’re going to do in this last formula is classify each date in our dataset as being part of the quote, Partial Current Period or the Partial Prior Period. Again, you’re not meant to sit here and watch me type at Playfair Data TV. I’m not going to type out– this out, but I’m going to walk you through the spirit of what it’s doing. And you can find this calculation below the video on the Related Content tab. What this is doing is saying– first of all, this first line is saying every date that’s going to be classified as Partial Current or Partial Prior needs to be less than the Maximum Date, that fixed level of detail calculation we did in step number one.

From there it’s saying if that date is also greater than or equal to the current week truncated at the weekly level, that’s our Partial Current Period. Moves on to the next classification. This time if the date is greater than or equal to the Maximum Date truncated last week, so last Sunday, and less than the order date truncated at last Sunday plus the number of days and range. That’s why we needed that calculation from step number two.

So Partial Current, if our highest date is a Wednesday, the Partial Current period will be Sunday through Wednesday, four days in this example. For Partial Prior, it’s looking at the last Sunday and adding the number of days in the range to get the same number of days from last week. So we got four days over four days. Let’s take a look and see how we did.

I’m going to add Order Date continuous to the Columns Shelf, and I will filter this down a little bit so that we’re just looking at the last two months in this use case. So we’ll say 10/1/2018 through 11/14/2018. Click OK. So my summary down here in the bottom left corner is telling me I’ve got 42 days on the view. Let’s see what happens when I color those marks by our Partial Current Versus Partial Prior classification. I will drag that to the Color Marks Card. And I see dots that are– we’ve got four dots colored orange and four dots colored red. So that sounds about right so far. Our Partial Current by default are colored orange. Let’s take a look. That would be Sunday, November 11, through Wednesday, November 14. So it properly classified the Partial Current period. And the Partial Prior Period will be Sunday, November 4, through Wednesday, November 7, so perfect.

Now that I have that classification, there’s a couple of things I could do with this. I could, for example, filter out only the period over period comparison that I care about. That’s one thing. So I could just filter out my null values, in other words. I exclude those, maybe make a line graph. I’ll look at Sales period over period and change this to a line.

There is an optional step in here where you can normalize these date ranges so that they line up on the same axis. There is yet another video here at Playfair Data TV that goes into detail on this technique. But essentially from here there’s just one last step. I always call it the Date Equalizer. It’s very easy to do when you’re comparing partial weeks over partial weeks because week numbers always have seven days in them. To do so, I could make a calculated field– I’ll call this Week Equalizer– and it will say, if this Partial Current Versus Partial Prior equals the Partial Prior, then I want to take my Order Date, but add a week to it. So it’s going to move those red marks up on the x-axis. So that they line up with the orange marks. If it’s anything else, we’ll just take the normal Order Date, and then END. Click OK.

And if I replace my Order Date, the default field, with my newly created Week Equalizer, and make that continuous, the red lines now line up with the orange lines. So it’s much easier to do a true period over period comparison. The Sunday from this week is exactly aligned with the Sunday from last week.

You can also use this classification with other date parts. You can actually do this for partial weeks, partial months, partial quarters, or partial years. To do so you have to update the date part in two places. I’m going to click OK. The first place is in your Days in Range. So I need to edit this calculated field. And this should always be day. So the first date part that you see in this Days in Range calculated field should always be day. But the date part that you can change is the second one. If you’d rather look at partial months instead of partial weeks, you would simply change this from week to month. Note these date parts are always lowercase to get this to work. But that’s the first place you would update it. You would also need to update– and note our axis changed a little bit there. But we’re not quite done. We also need to change our classification field. In this one, every place that it says “week,” we want to replace that with “month.” So, month there. Month there. And we’ve just got a couple more. And month.

So every occurrence of week I replaced with month. Click OK again. Note it looks like it’s working. We’ve got longer periods because we’re now comparing 14 days to 14 days. However, they’re no longer lined up. That’s because we did a week equalizer, and we only added seven days to the x-axis for the red marks. That date equalizer doesn’t work quite as well when you’re dealing with a month date part because months have anywhere from 28 days to 30 days to 31 days.

But I might show you one other thing we can do with this. First let’s quality check it. I’m going to replace the Week Equalizer field again with our default Order Date field. And I’ll just turn this back into circles so we can just take a closer look. So we should have– and I’ll get rid of our filter here as well. It should this time be the first 14 days in November compared to the first 14 days in October. So we’ve got November 1st to November 14th, perfect. And October 1st to October 14, perfect.

One last way we can use this, now that we have classified each date as being in the Partial Current period or the Partial Prior period, we can isolate the performance for each one of those. So we could call this Partial Current, and if our KPI is Sales, we could say something like, SUM if this field is Partial Current, then Sales, END. That’s just isolating sales for the first 14 days in November in this exact use case. Click OK. Now that I’ve isolated the Partial Current Sales, it’s much easier to isolate the Partial Prior Sales. I can do that by duplicating this calculated field and just make a couple of tweaks to it. First of all, the name. This will now be my Partial Prior Sales. And I need to change this classification from Partial Current to Partial Prior. Click OK.

Now that I have those two things isolated, I could put them on a view by themselves, for example. I’ll just make this a text table for now so we can see the numbers. So that first line is showing that the first 14 days of November have generated roughly $55,000 of sales. That same 14-day period last month we generated $38,000 of sales.

Now that those are isolated and working– by the way, this is all automated. It will always be based on the maximum date in your dataset, so whenever you open this up, it will automatically be refreshing for you and filling in dates.

One last thing I’d like to do with this is maybe compute some kind of change. So if I call this Period over Period change, if you wanted to do a percent change, the formula for that would be the Partial Current Sales divided by the Partial Prior Sales. Just to be sure it’s doing that right, I’ll put it in parentheses, and then minus one. That will get you a percent change.

There is another video here at Playfair Data TV that shows you how to compute these types of changes, and they are used to create one of my favorite dashboard elements. I call it the period over period index callout, if you want to check that out. But I’ll click OK. I will add this as a third row. Note it’s zero, that’s just because I need to change the default properties. I’ll make it number percentage with two decimal places. And just to be sure, I always quality check these metrics, these calculated fields. So we had 54,816 divided by 38,374. That is an increase of 42.85%. Perfect.

This video has showed you how to automatically compare the last two partial date parts in your dataset. There’s lots of applications for this.

This has been Ryan with Playfair Data TV – thanks for watching!