Innovative Tableau

Chapter 67. 3 More Innovative Ways to Use Dates: Tip 2

How to Compare the Last Two Partial Weeks, Months, Quarters, or Years

This chapter shows you how to automatically isolate the last two partial date ranges so that you can compare an equal number of days period over period. In Chapter 66, I show you how to compare the last two complete date periods in Tableau, but sometimes you want the comparison to be even more current. For example, you might want to compare this week to last week even if the week is not yet complete instead of comparing the last full week to the full week from two weeks ago.

The benefit to this is timelier analysis, but you can often end up with an apples-to-oranges comparison. For example, if it’s Wednesday this week and you try to create a week-over-week analysis, you can end up comparing the three days from this week to all seven days from last week. The tricky part to creating a true partial period-over-period analysis is that you need to calculate the number of days in the current range and then cap the last full date range at that same number.

This chapter shares the formulas needed to automatically create a partial period-over-period analysis so that both periods—whether you’re using weeks, months, quarters, or years—contain an equal number of days.

The core of this technique is the same as when comparing the last two complete date parts in that we need to create a calculated field that classifies dates as current or prior. Partial periods are more ...