<< Back

How to show dynamic date ranges

Descriptive titles are really important when making vizzes. They show your uses, at a glance, what you want them to know. They also mean that users don’t have to hunt through different legends around your dashboard to understand what it is they’re seeing.

When showing trends over time, I’ve taken to including the date range in the title of each viz, so my users don’t have to scan the axes. This also means I can make my axes less prominent, giving more attention to the data I want to show.

I would normally use FIXED Level of Detail calculations to return the maximum and minimum dates in the data-set and then add those calcs to my sheet titles. This worked well until I had filters applying to the sheets, then I had to either edit my LODs to INCLUDE/EXCLUDE or add the filters to context. Not the end of the world, but at this point I’m writing multiple calculations to give me one line of text in a chart title so I went looking for a better way.

Window functions to the rescue

Window functions are a special type of table calculations in Tableau. They allow you to define a ‘window’ over which their aggregations apply. To quote Tableau:

The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.

Tableau’s online help

The key thing to take on here is that if we don’t explicitly define the window, Tableau will take that to mean everything in the view should be included.

Properly set up, WINDOW_AVG(SUM([Sales])) will return the average of all the sales in view. If we change what’s in the view by applying filters, this average will likewise change.

So we can use WINDOW_MIN(MIN([Date])) to return the minimum date that is in the view. That’s not surprising, but did you know you can also use it to return the maximum date in the view at the same time?

Setting it up

Category sales

Here I’ve shown the sum of sales by category for each week. (The context filter isn’t important – it just filters different categories to different date ranges. Axis date is a date field truncated to a parameterised value)

Next up I write a calculated field called ‘Date’ as follows:

This field returns the minimum date found in the window. Window functions require their fields to be aggregated. In this case I went with MIN(), but for our purposes we could have used MAX() or AVG() as they would return the same value.

We then add this field to detail setting it up as a Continuous field and computing it using ‘Cell’.

By computing using Cell, the window function returns the minimum date for every date/category combination. In other words, it returns every date. By ensuring the field is continuous, Tableau will give us the range of values rather than ‘All’.

We then add this field to our title (in ludicrously big font) and click OK. Now we have our lovely date range title, which updates whenever filters are applied.

No one’s going to miss that date range

Why use a window function?

In this situation, we could achieve the same affect by using Axis Date in our title. However, if we use a discrete date field, or combination of discrete and continuous then our title wouldn’t work. It would return ‘All’ rather than the actual range of dates. We might try to counteract this by adding a continuous version of the same date field onto detail, in the hope of using that field in the title. However, this can break up our view and turn our lovely line charts into unintentional dot plots. The window function falls foul of neither of these traps.

Using discrete dates won’t make nice titles
Adding a duplicate continuous date field on detail breaks up the view

So there you have it, one simple calculation to accurately title all your dates. You can find an accompanying workbook to this blog here, and if you have any questions you can find me on twitter @olliehclarke.

Ollie Clarke

London, UK

7 thoughts on “How to show dynamic date ranges

  1. Hi Ollie- Thank you for this amazing windows_max function used on charts to dynamically change dates. I am not having much luck using the same concept on tables with dates using the compute using cell method. Is there a way around the windows function to get the range of dates addressed in the table? Any help is appreciated.

    1. Hey Shireen, have you made sure that the window calc is continuous? If it’s not when you try to reference it in the title, it will say ‘All’ instead of the date range that is present.

  2. Thank you for this information. It is nearly working in my case, but their is an unexpected behavior in the date ranges this technique produces: the end date returned sometimes varies by the way the data is displayed (not filtered). For example, I have a table with the counts of records by year: when I descritize the rows by one dimension (age group) the end date is “… to August 2020”, but when I have the same data descritized by another dimension (race/ethnicity) it gives “… to September 2020”. I have not figured out why, since the data adds up to the same total number of records.

  3. Thanks so much for this, I spent an hour trying other solutions, but this one works! I like how you take us through each step in detail.

Leave a Reply

Your email address will not be published. Required fields are marked *