Tableau Deep Dive: Dates – Rolling Dates

Data

Tableau Deep Dive: Dates – Rolling Dates

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

Let’s continue our exploration of dates in our Deep Dive. In Part 6, we’re going to focus on how we can create rolling dates. A rolling date is taking a specific date, such as order date, and then adding a certain amount of days, months or whatever date part we need. For example, a rolling year would take the original date and set the ending time frame up by one year.

There are a couple of ways to do this – using a continuous date filter or using calculated fields. Each one solves specific use cases.

Relative Date Filter

Continuous dates filter just like continuous fields with one big exception. We gain an extra piece of functionality – relative dates. A relative date filter allows us to pick a date and then define the size of our window to filter. Let’s take the following view and create a 90-day relative date filter from our current date.

Create date filter

I’ll right-click drag on Order Date in the data pane and pull it onto the Filters card. Doing this is a short cut which will take me right to this helpful menu:

Filter Field (Order Date)

I want to select Relative Date and click Next. Now I get the continuous filter window:

Relative dates

There are two important aspects to the options presented here for a relative date. The first is what is going to be the anchor date? You have the option of selecting yesterday, today, tomorrow or choosing a specific date with Anchor Relative To. The second piece is choosing your date part and the units. Do we want last seven days? The next six months?

Let’s set up our relative date filter for the previous 90 days from today:

Relative dates - 90 days

Our view will now only show sales performance from approximately the three previous months (90 days). To help our user, let’s add some context into our worksheet title for a final view like this:

Updated view

Easy as pie!

Rolling Date Calculated Field

Our next use case examines a need to show performance date from a rolling date relative to a specific dimension member. The relative date filter above filters the entire view on a specific range of dates. In our next view, I’m going to create calculations that will show a rolling year of sales for each row in my table. Let’s say I want to visualize first year’s sales from my phones sub-category from the date of their respective product release.

We’ll need to start with some calculations. Level of Detail calcs to the rescue! For more information on LODs, check out that Deep Dive series. The first thing I need to do is to create my rolling year for each product:

Rolling Year calculation

Let’s break down this calculated field, because there’s a couple of different things happening here. First, I’m creating a rolling year by using DATEADD to add one year to the first order (i.e. minimum of Order Date). Second, I’m using an LOD to fix it to product name, so each product will have a unique date value. Finally, I’m wrapping the whole thing in the DATE function so it doesn’t return as date and time. All of my date fields in Sample – Superstore are date only, so I’m just being more precise in choosing my data type.

Next, we need to create a calculation that will determine our sales in that first year:

First Year Product Sales

Remember, do not add an aggregation to Sales, else your calculation will break as Order Date is not aggregated. Now let’s build our view. I’m going to add Sub-Category to the Filters card and select only phones. Next, put Product Name onto Rows and sum of First Year Product Sales onto Columns. Finally, let’s tighten up our view by showing only the top 20 products:

Filter [Products Name]

I’ll need to make Sub-Category a context filter, too. Otherwise, our top 20 products will be picked from all of the product offering. Right-click on Sub-Category in the Filters card and select Add to Context. Here’s our final view:

Final view

If you wanted to add a bit more information to the table, we could add our MIN(Order Date) and our Product Release Date – Rolling Year fields as discrete exact dates to Rows, as well. That gives us this view:

Adding more info

This same logic could be applied to other analyses, such as customer cohorts.

What’s Next?

I hope you find these helpful. In our next article, we’ll create a calendar style filter that you can use on a dashboard. Until then, please let me know any thoughts or questions in the comments section below. Cheers!

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Analytics Consultant | ANZ Practice Lead
Tableau Design Tips: Using Images in Your Dashboards Images on your dashboard, combined with visual best practices, make a huge difference when it comes to the impact of your work. My ...
Tableau Data Viz: Genocides of Today This visualization has a personal aspect for me. In April 2017, I was approached by the Tableau Foundation to volunteer my Tableau ...

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