How to Use Parameter Actions to Change Date Parts in Tableau

Conveniently change line graphs between days / weeks / months

Make your line graphs more useful by allowing your audience to decide the granularity of the date being used with just the click of a button! We’ll be using the new Parameter Actions feature available as of Tableau Desktop 2019.2.

Calculated field that truncates dates at various date parts:

DATETRUNC([Date Part Parameter],[Order Date])

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to use parameter actions to change the date part of a line graph in Tableau.

This is a rerecord of a video here at Playfair Data TV on the advanced track that showed you how to do the same thing using the Parameter Actions extension that you had to download from the Extensions Gallery. But Tableau has actually made this a default, out of the box, technical feature of the product as a version 2019.2. So I’m rerecording it.

It’s a lot easier now. To be honest, this probably isn’t very advanced anymore. It’s more– should probably maybe be in the Tips section. But I think it’s a very relevant tactic that you’ll get a lot of use out of. So I just want to show you an easier way to make it.

Over here in Tableau Desktop, note that I’ve got two different data sources. So the Sample Superstore will be used to make our line graph. But I’ve got this dataset that will be used as the control sheet. This is what will eventually allow our end user to click on a specific date part to filter, or control the input of the parameter rather, that’s affecting our line graph.

It’s a very simple Excel file. If I view this underlying data, that is the entire file. It’s four rows. We’ve got some text that will be used on our buttons in the first column. And then we’ve got our four different date parts in the second column.

Note that those values are all lowercase in that second column. To use this function, the DATETRUNC function, the date parts have to be lowercase. So I just went ahead and made those lowercase in the underlying data. I’m going to exit off of this.

And let’s go ahead and create our line graph. That’s an easy one. So we’ll knock that out. We’ll just look at Sales by continuous date, so the very first option. There’s our first line graph.

I’m also going to make a parameter. And I’ll call this my Date Part Parameter. And the data type is String. And it is a list of specific choices.

The control sheet is going to overwrite the Display As column for this tactic. So that doesn’t matter at all. But the values, again, they need to be lowercase to work with the DATETRUNC function to truncate our line graph at different levels. So the options were day, week, month, and year. I’m going to click OK.

And next I need a calculated field that does that truncation. I’ll call this Date with Date Part. And the entire formula is DATETRUNC…

What comes next is our date part, which is going to be controlled by that Date Part parameter. So I’ll put that in there next, followed by a comma, followed by whatever date we are truncating. In the Sample Superstore dataset, that date is called Order Date. So that is the entire formula. I will go ahead and share this in the related content below the video too so you can just copy and paste it. But I will click OK.

I will replace the original Order Date with the newly created Date with Date Part. If we did this right, we should not see anything change, because the current value, which is always the first by default, it’s the first allowable value, is day. We were already looking at our dates by day. So we just replaced day with day, essentially.

But now that we are using this parameterized version of the date, if I show the parameter control, I can switch from day to week to month to year. This is really a handy tactic anytime you’re filtering your dates by different ranges. For example, this doesn’t look great when I’ve got four years worth of data points. But if I had filtered this down to just one week, I probably would want to look at it by day. If it was any other date part, I would just see one data point. That wouldn’t be very effective to help me look at the trend over time.

So this is already one of my favorite tactics. But I’m just showing you some ways to make it better. First we’ve parameterized it. Now we’re going to go in to take it a step further and improve the user experience by using a control sheet, then pairing that control sheet with parameter actions to allow you to just click a button to change the truncation of the date.

So I’m going to make a new sheet. This one’s going to be built with my Date Part control sheet. And I’m just going to throw together a quick table that has our button text.

Maybe I’d rather make this horizontal. And I’ll hide this header. And you could do a little bit of formatting. These are meant to be buttons. But I’ll let you worry about that on your own.

I’m also going to put the Value field onto the Detail Marks Card just so that it becomes part of this sheet. That’ll be part of the data associated with this sheet. Note it didn’t change the view at all. But later on when we go to set up our parameter action, we’re going to want that value with the lowercase date parts to be associated with this sheet. So I’m going to call this my Control Sheet.

And now I’m ready to go make my dashboard. Actually, one last thing. Let me show that header again and maybe put these in order at least, so day, week, month, year. Now I can hide that header.

Now I’m ready to go make my dashboard. And I’m not going to make this pretty for you but just to show you the functionality. I’ll throw the control sheet as well as my line graph onto the view. So control sheet’s in the top left. Line graph’s in the bottom right.

Because I had showed the parameter control for this line graph, it shows up here in the top right corner. But eventually we’re not going to need that. This control sheet’s going to replace that parameter control.

To do so, go to Dashboard in the top navigation. Click Actions, Add Action. This is the new one as of Tableau version 2019.2. I’m going to change the value that is being used in that calculated field to truncate the dates on our line graph. Click change parameter.

This sheet, the Source Sheet, is the Control Sheet. So I’ll deselect everything but the Control Sheet. We’ll have it run on Select, which is synonymous with click. So if you click on the control sheet, we want it to change the allowable value that’s feeding the truncation calculated field.

The parameter that we’re targeting is that Date Part Parameter that we set up earlier. In the field, this is why we wanted to put that Value dimension onto the Detail Marks Card, so that it would be on this sheet. These values are lowercase, so they will work with that date part function.

Choose Value. Aggregation we’ll leave as None. Click OK. Click OK again. Didn’t see anything change. But now if I click on a different date part, note that my line graph is being truncated by whatever I click on. I now no longer need this parameter control, because it is being controlled by this control sheet.

Just a nice functionality. Again, I love the practicality of being able to allow my audience to change the date part from day to week to month depending on how many data points are in that range. And we’ve just improved the user experience, which was made possible through the brand new parameter actions as of version 2019.2.

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