Allowing the user to choose the time unit for reporting

One of the loveliest little things I’ve discovered in Tableau is a little technique to allow the user to select the time unit used for reporting: years, quarters, months, weeks, days etc.

I’ve used the technique on my #GCloud dashboard, which reports on the UK government’s use of its #GCloud framework for IT service provision. On the right-hand side at the top, you can use a slider to select months, quarters or years. (The data behind this one is presented at the month level, and so there’s no need to go down to weeks or days, but you could if you wanted to.)

To do this, create a new parameter. Let’s call it Time Unit. And make it a string, with a list of the time units that you want to use. In this case, monthquarteryear. Make sure they are all in lowercase (no leading capital). If you want them to appear with leading capitals in the dashboard, then add these as “Display As” names (e.g. Month, QuarterYear).

In your main dataset, let’s say you have a field called Sales Date which contains date values. Now, create a new field in your main dataset, called Reporting Period, say, and give it the following formula:

datetrunc([Time Unit],[Sales Date])

This will truncate, or “round down” your sales date based on the time unit selected in the parameter. So if you select Month in the parameter, your new field will round down your sales date to the first of the month; Quarter will round it down to the first of the quarter, etc.

And you can now use this new Reporting Period field instead of your Sales Date field in your chart. Right-click the pill and hit Exact Date; and right-click again and hit Discrete.

Add the parameter field to the view to allow the user to control it. I always like mine to be a slider, but you can choose to use radio buttons or a dropdown selection box. Whatever floats your boat.

Let me know how you get on!

2 thoughts on “Allowing the user to choose the time unit for reporting

Leave a Reply

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