Using Tableau’s Parameter Actions Extension to Change Date Parts

Truncate line graphs with one of Tableau’s newest features

Learn how to make your line graphs more useful by allowing your end user to decide the granularity of the date being used. We’ll also use Tableau’s new Parameter Actions extension to create a web-like user experience.

Calculated field referenced that truncates the date at the date part selected:

DATETRUNC([Date Granularity],[Order Date])

Hi, this is Ryan with Playfair Data TV. And, in this video, I’m going to be sharing one of my favorite advanced tactics, which is to allow your end user to choose how a date is being truncated on a line graph. But we’re also going to take this a step further and use one of Tableau’s newest features, which is called Dashboard Extensions to create a slicker user experience, to allow my end user to choose what the date part being used is.

First, to show you why this tactic is useful, I’m just going to throw together a quick line graph that looks at sales by continuous order date. I can make it continuous by right clicking Order Date, dragging it to the Columns Shelf, and choosing that first option– Order Date Continuous. As you can see, this chart is really hard to read. We can see the spikes, but it’s hard to evaluate the individual trend of days just because we have so many data points on that x-axis. We could make this a little bit more readable if we were to add a range of dates filter, and maybe set this to just be the last month of the data in the sample data set, so December 2018.

So now, it’s easier to read, but what happens if we wanted to look at that bigger date range and go back and forth? So the first thing I’m going to show you is how to create a parameterized date part selector that allows you to choose what the date part is in a line graph. It involves a parameter, so I’m going to click a parameter and call this Date Granularity. And let’s say that we want to allow our end users, as well as ourselves, to choose from four different date parts– day, week, month, or year.

Those are words, so I’m going to choose a data type of String. And it’s a very short list of options, so I’ll just choose a list of allowable values and type each one of those date parts in as the values. Very important thing to remember for this technique, the values have to be lowercase in order for Tableau to recognize them later on when we go to truncate our dates. So I’m going to do day lowercase, week lowercase, month lowercase, and year lowercase. You could change the capitalization on the date parts being displayed that way they show up with a capital letter when they’re shown on a parameter control. That’s optional. That’s purely for the way that it looks. And I’ll click OK.

In order to get this to work, we have to set up the following calculated field. I always call this Date with Granularity. And the formula is DATETRUNC, the parameter that we just created, so date granularity, comma, whatever date we’re using, so, in this case, Order Date, close parentheses. I’m going to click OK. I’m going to replace our original Order Date with the newly created Date with Granularity. There’s a critical step, which is to make sure you choose the first choice. We still want this to be continuous, so I’m going to choose that first choice and click OK.

The trend looks exactly the same, and it’s because the current value was day. We were already looking at this by day, so the trend itself didn’t change. You could confirm the current value by right clicking on that parameter and clicking Edit. And you can see the current value is day. And now, if I show the parameter control, my end user will be able to choose to look at this line graph by day, by week, by month, or by year.

And notice there’s only one data point for month and year. It’s because we had added a filter that’s looking at just December 2018. If I want to remove that, we now see four years worth of data points when it’s being truncated as year and 48 data points when it’s being truncated as month. But whatever is chosen in this parameter control is the date part we are truncating our order date by.

That’s a technique that I’ve used for a long time, and I really like it. But we’re now going to take this a step further using Tableau’s new Dashboard Extensions to create a much nicer user experience. Instead of having this dropdown menu to choose the date part, we’re going to set up a control sheet and allow our end user to just click on a word, which will then populate the date granularity of this line graph.

To get this new user experience to work, like I said, we’re going to be using this newly created or newly released feature of Tableau called Dashboard Extensions. This came out with version 2018.2 of the software. If you’ve got that version or later, just navigate to extensiongallery.tableau.com, as pictured on the screen. This is in beta, technically, at the moment, but it is available as of version 2018.2. You can browse through here and see all the different types of extensions.

These extensions are ways for third-party developers to take the core Tableau software a little bit further. Some of these are paid. Some of these are unpaid. But there’s some really good ones that are already available to you that are unpaid. And some of those have been coded by Tableau themselves. The one that we’re going to be using for this use case is called Parameter Actions. So I’ve downloaded that.

And I’m going to go back to Tableau Desktop. And before we actually implement that dashboard extension, I’m going to make a control sheet. I’ve connected to this secondary data source. And just to show you the data, it’s a very simple data set that just contains two columns– the name that’s going to be on the button that my end user will eventually have access to click on and then the underlying values of those four different buttons.

And notice those are the lowercased date parts because those are the words that are going to populate that date truncation of our order date. So that is the entire data set. I’m going to throw together a sheet that will just act as our control sheet by putting Button on the Rows Shelf. Actually, I’ll put that on a Columns Shelf to make it horizontal instead of vertical. I will also put Button on the Text Marks Card. I’m going to reorder these by putting week after day just by dragging the column header into that second position.

But now that this is sorted in the right way, I’m just going to hide that header because it’s duplicative. So I’m going to deselect Show Header. And we’ve just got this little control sheet with our four different date parts in it. It’s very important when you’re creating these control sheets that the value itself that’s going to populate that parameter is on the Detail Marks Card because that information needs to be part of this worksheet in order for it to work eventually. So I just drag Value to Detail. But that is the entire control sheet.

I’m now going to combine these two things on a dashboard. I go back to my dashboard and drag Sheet 1 on the view and drag Sheet 2 to the very top of this view. I will also hide these headers. And the experience that we’re going to create is if you click on one of these letters in the control sheet, the underlying values of the date part is going to populate the truncation of this line graph. So just a little bit more slick. It looks more like a web app versus having this dropdown menu.

In order to achieve that user experience, we’re going to use an extension object. So this is that new feature Tableau Dashboard Extensions. If you’ve got version 2018.2 or later, you will see something called an Extension. I’m going to drag that onto the view. Don’t worry at this point how large that is looking on your dashboard. I’m going to show you a way to clean this up later. So I’m just going to drop it there for now. It’s going to ask me to choose the location of my extension. I’ll click My Extensions.

You can save this anywhere you’d like once you download it from the gallery. I recommend that you choose either the Tableau Repository. There’s even a folder in there called Extensions, or you could save it in your Tableau Program Files. It doesn’t really matter where you save it as long as you know where to find it. But these extensions end with the extension .trex. And the one that we’re using right now is right here– tableauparameteractions.trex. I’m going to click on that.

It’s going to open this dialog box. Notice that it says no open input parameters. There’s one change I need to make in order to configure this dashboard extension. I need to make a parameter with allowable values of all. You have to kind of unlock the parameter allowable values, because remember, it’s going to populate the allowable values with whatever is clicked on a different sheet.

So before I configure this, I need to go back to any worksheet. And I’m going to change that Date Granularity parameter so that the allowable values are All. And as long as there’s still a current value in here, this will work while we are configuring it. So we’ve got month still in there, but we just changed it from a list of four choices to allowable values of all. I’m going to click OK.

Now, I’ll go back to my dashboard. And notice before that dialog box popped up as soon as we added the extension, but you can also configure an extension once it’s already been added to the view. And that’s what we’re doing now. So this is the extension. I can still configure it by clicking on the object, which has been selected. I could tell by that gray outline around it. And if I click this down arrow, the first choice is Configure. Now it’s working for me, so that wasn’t in there before. But now that the allowable values are all, it has selected that Date Granularity parameter. I’m going to click Set.

Now, I choose which field to populate that parameter. Button, remember, are those four letters. Those are not date parts, so that would break our formula. It wouldn’t know which date parts were associated with each of those four letters. I want to click on this dropdown and choose Value. And this is why it was so important to get that value dimension onto the Detail Marks Card of our control sheet. If we had not done that, the value information would not be part of that worksheet and, therefore, would not be here as a choice. So I’m going to click Value, and I will set that.

And we’re saying that this dashboard action or parameter action originates at Sheet 2, so that’s what’s been selected. And that’s it, so we’ll click OK. Now, if I choose day instead of month as the date part, my line graph is broken down by day. If I choose week, it’s broken down by week. And year, it’s broken down by year. Also notice that it’s populating that original parameter control. But that’s no longer how we’re controlling the date part being used in this line graph, so we no longer need it. So I’ll just exit off of that.

And also notice this gear icon, which is the extension is taking up way too much of the view. There is an easy way to clean that up. Just click on the dashboard extension. Click this down arrow that appears and make it floating. Once that object is floating, you can click on the Layout tab and change the width and height, as well as its location on the x and y-axis. So I’ll just make the width and height 1 pixel so that it’s a very small object.

And if I want to know exactly where that object is located, I can also change its x-location to 0 and its y-location to 0 so that I know it’s tucked up there in the top left corner. Now when I click off, it’s invisible. It’s not affecting my view at all. And if you’re ever worried about finding it again, just navigate to the Layout tab. And there it is– Parameter Actions. If I click on that, it has selected the extension. I can still click into it, change the configuration if I wanted to. But I just wanted to get it off of the view for now.

So there’s my final view. Obviously, I could format this a little bit further, but we’ll call this my final view. And it’s just a nice user experience, looks a little bit better, and it allows my end user to change the truncation or granularity of a date. If I choose day, I’m looking at sales by day, week is sales by week, and month is sales by month.

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