Using Parameter actions in Tableau to filter sheets: Quick Tip

0:00Hey, it's Tim here. In today's video I want

0:01to show you a really simple trick you can

0:02do with parameter actions.

0:04Essentially I want to use a parameter to

0:06change the date in which the chart starts

0:08in, then I want

0:09a simple way to reset the chart once I've

0:12changed that date. This is essentially a

0:14technique you can

0:15use in lots of different instances, but I

0:17just want to show you the mechanics of how

0:19it works.

0:19So you can see here I've got the chart and

0:21if I go to this particular date and I

0:23select September,

0:24you can see that the range for the chart

0:26changes based on that date that I clicked.

0:28So now the date starts on September 2017.

0:31If I go here to September 2018 it does

0:34exactly the same

0:34thing and now the range is much smaller.

0:37Now if I want to reset the date I've got no

0:39way of doing

0:39that. Now if I publish it on Tableau server

0:41or Tableau Online I can of course use the

0:44revert

0:44button to get back to the start, but I'd

0:46like to give a clear way on the dashboard

0:47for someone to

0:49reset this. Essentially what I've got up

0:51here is a button that allows me to reset

0:53the sheet and it

0:53goes back to the original time range. So

0:55how does this work? Let's sort of tear this

0:58down a little

0:58bit and start from scratch. I'm going to go

1:00to a new sheet, we're going to just start

1:02building this

1:02out. Okay so what I'm going to do is I'm

1:04going to start by dragging Order Date into

1:06Columns

1:07and Cells into Rows just to get us a line

1:09chart and ready to go. And then what I'm

1:11going to do

1:12is I'm going to turn this into a continuous

1:14month, essentially using the continuous

1:17variant of the

1:18month which is green in this case, that's

1:20why this has changed to green. And what you

1:22can now do is

1:23we can start to sort of think about the

1:24mechanics of how this parameter action is

1:26going to work.

1:26Now essentially what I want to do is when I

1:29click on this particular date I want this

1:32sheet to send

1:33an instruction to a parameter and then what

1:36I need that parameter to do is essentially

1:40filter my

1:40visualization. So if you think about it I'm

1:42essentially using a parameter action to

1:45filter

1:45this sheet and in order for that to work I

1:48need to have something in the Filters pane

1:50in order for

1:51this to work. So I need to have something

1:53already in my Filters pane listening out

1:54for my parameter.

1:55So let's go ahead and sort of build the

1:57scaffolding for that. I'm going to call it

1:59something else,

2:00you can see I've already got an example

2:01here but we'll just go and build a brand

2:03new one.

2:03So the first thing we'll do is we create a

2:05parameter, not a calculation, a parameter,

2:08and once we've created a parameter we can

2:11give it a name. So we can call it

2:13a Date Param, okay. And we want to make

2:16sure that we pick a date item here. Don't

2:18choose

2:19date and time, it has to be a date and it

2:20doesn't really matter what value you've got

2:22there but what

2:23you might want to do is set it to a value

2:25that makes sense. In this case the smallest

2:28date item

2:28here is actually on the 1st of January 2017

2:33. So what I might do is set it to the March

2:372017. So

2:38I can just type into this date field here

2:40just by doing that and typing 2017 and I

2:45can just go click

2:46on October and select March and that takes

2:48me right to the date and I'll just select

2:50the 1st of

2:51March 2017 as a date and select okay. So

2:54our parameter is set up. If I actually

2:56right click

2:57and show this parameter you can see that it

2:59's over here on the right hand side with

3:01that specific date

3:02and I can easily change it. Now for the

3:04next bit what we need to do is to put this

3:05in the filter

3:06somewhere so it's filtering this

3:08visualization and we can kind of test it

3:09with this slider here

3:10once we change the date. So let's go ahead

3:13and create a calculation and I'll make this

3:15larger

3:15so you can see. So for this one all we need

3:17to do is make sure that we are only

3:19bringing back dates

3:20that are greater than or equal to the

3:23parameter. So it's pretty easy all we need

3:25to do is say

3:26we can actually do this instead of trying

3:30to type out the date part we can actually

3:33just go

3:33and specifically hold control or drag and

3:35bring this in and this will actually give

3:37us the date

3:38part as a calculation in this case rather

3:40than what we see at the top. And all we

3:42want to do is

3:43we want to basically check whether that

3:45date is greater than or equal to the date

3:48parameter. So

3:48we can just say date param and you can see

3:51that it's purple because parameters are

3:52typically

3:53purple in calculation windows. So in in

3:55sort of plain English we're checking

3:57whether the

4:00date in the chart is greater than the

4:07parameter. I've got an old habit here where

4:11I use the

4:12forward slash I've just gotten used to it I

4:14just type it all the time. You can do a

4:16forward slash

4:17star before someone tells me you can do

4:19that and type your comments in there as

4:21well that is also

4:22fine and works completely well. Okay so let

4:25's just clear that and we've basically given

4:27the logic

4:28here I'll call this my date filter. I'll

4:30call this version two because I've already

4:33got one in the

4:33calculation in the window. So click apply

4:36and click okay and now when I drag this

4:38into filters

4:38you'll see that we get a boolean response.

4:41Essentially if the dates are greater than

4:43or

4:43equal to the parameter then it's going to

4:45return true and if not it's going to return

4:47false. So

4:48what we want to do is keep all the values

4:50that are true essentially everything bigger

4:52than the

4:52day in our parameter. So now let's hit

4:55apply and click okay and now you can see

4:57that this is

4:58working the date here is first of March

5:002017 and just so we can make sure this is

5:03working nicely

5:04let's go to June of 2017 and you should see

5:06the chart changes there nicely the June

5:09value is 34,000

5:11and if I just go back to March or April

5:14even or do and we go back to the June value

5:17it's still 34,000.

5:18So everything is working nicely now for the

5:21final step. Okay so for the final step what

5:23we're going

5:23to do is build a dashboard because what I

5:25want to do is make sure I've got everything

5:26that I need

5:27in view. So I'll go ahead and bring in

5:29sheet three this is the one I was actually

5:31working on

5:32and you'll notice here when I dragged it in

5:34I've actually set it to floating so let's

5:37close this

5:38again and switch over to my tile set up

5:40here on the bottom left go back in drag in

5:43sheet three

5:43and everything is pretty much good to go.

5:46Now I have got my date parameter here but I

5:48'm going to

5:48leave that there just so you can see this

5:50working essentially and what I want to do

5:53is essentially

5:54set up this action to change that parameter

5:56and that is essentially a parameter action.

5:59So I'll go

5:59over to the dashboard at the top go to

6:02actions I can also hit ctrl shift d add an

6:05action and you

6:06can see I have here a few options for

6:08actions I'm going to choose the parameter

6:11action and you can

6:12see here that I've got this very simple

6:14option and I'm essentially coming from

6:16sheet three which is

6:17the one I've just created it's going to be

6:19using a selection so it's going to run on

6:21selection

6:21and then what I need to do is change a

6:23parameter and the parameter is going to be

6:25the date parameter

6:27and the field I'm going to be targeting is

6:29the month of order date which is actually

6:31what's in

6:32our visualization here you can just see

6:34just outside of this option and that's

6:36pretty much it

6:37everything else is is pretty nice now what

6:41we can do is we can say that when you clear

6:43the selection

6:43you go back to a certain date that's one

6:45way of doing it but I don't want to use

6:46that that can

6:48kind of lead to a very confusing dynamic

6:50when the user tries to use it so let's hit

6:52okay and let's

6:53try out this parameter so I'm going to try

6:55and click on November 2018 and we should

6:58see two

6:58things the chart should almost have half

7:01the date range available on our axis and

7:04this day here

7:05should change so let's go ahead and click

7:06that and you can see that it's done that

7:08really really nice

7:09thing so we've done the sort of first final

7:11first step and we're pretty much ready to

7:14go see if I

7:14click keep clicking on these it just goes

7:16back but now we have a problem because you

7:18can see here that

7:19I've almost clicked my way out of this

7:21chart and the only option I have is to

7:22revert the visualization

7:24or manually change the parameter we don't

7:25want to manually change the parameter I'd

7:27love to give

7:28someone a button that automatically brings

7:31back the whole extent of the data so in

7:33order to do

7:34this there's a little bit of sort of

7:35thinking you have to do here what you want

7:37to do is essentially

7:38bring back the whole date range and the

7:40whole date range is is going to have to be

7:43a calculation that

7:44dynamically finds the whole entire range

7:46now you can do this one of two ways and the

7:48way I'm going

7:49to do it is use an LOD because essentially

7:51that brings back the date in the whole

7:53entire data set

7:54so I'll go ahead and create calculation and

7:57an LOD is a level of detail calculations if

8:00you haven't

8:00seen that go check out my video on that I

8:02explain how they work in lots of detail but

8:04nonetheless

8:05let's make this larger and we can just go

8:08in and type what we're going to do we don't

8:10even actually

8:11need to do any fixation or anything all we

8:14need to do is for the entire data set bring

8:16back the

8:17smallest date so you can actually do an LOD

8:20where you don't declare any sort of

8:22calculation this is

8:24like I don't know what to call this I can't

8:25remember I can never remember the term for

8:27this

8:27but essentially it's an LOD where you don't

8:29define anything and so you can just say min

8:32actually once you use auto complete and

8:35then just get order date so what this is

8:38going to do

8:39it's going to look at the entire data set

8:41and the entire data set that's actually

8:44accessible and

8:44this LOD runs after a context filter so if

8:48you've got this set up you know using

8:51context filters

8:52those are still going to work but it runs

8:54before dimensional filters so if you've got

8:56a filter here

8:56in your dates and you want it to work it's

8:58still going to bring back the whole range

9:00of the data

9:00sets it's kind of handy because if you've

9:02got a date filter working as well it's

9:04still going to

9:05bring back everything before the date

9:08filter is processed so here we'll just say

9:11smallest date

9:12and now we have the smallest date we can

9:14actually bring it into the view and we're

9:17going to hit

9:17apply and click ok and now what we want to

9:19do is bring it into a new sheet the reason

9:21we want to

9:21bring into a new sheet is because this

9:23sheet is going to act as a button we still

9:25don't have like

9:26amazing tight control of buttons yet I'd

9:28love to be able to use navigation buttons

9:31to do things

9:31with the data set but that's not a feature

9:33just yet I hope it will be in the future I

9:35hope someone's

9:36working on that but nonetheless if I grab

9:38the smallest date here and I just put it on

9:40text you

9:40can see that it's 2017 and then if I go to

9:43the end of actual actual day itself you can

9:46see that

9:46it says the 3rd of January 2017 is the

9:49first date available in the data set so

9:51that's pretty pretty

9:52good we have a sort of nice way to you know

9:55find the smallest date in our data set and

9:57this will

9:58stay true whatever happens so even if I run

10:00a context filter this is still going to

10:02work

10:02and what I want to do now is just go into

10:04this text label and just change this and

10:07the value is

10:08in the visualization it's on the marks pane

10:09but it doesn't actually need to be

10:11displayed anywhere

10:12I just want to turn this into a nice button

10:14so I'm just going to call this reset my

10:16view

10:16hit apply and click a hit ok and you can

10:19see that that's it that's all it's going to

10:22do and there's

10:23a few formatting problems here if I just if

10:26I just go into this actually let's go into

10:29alignment here

10:31and let's make sure this is absolutely

10:33centered so now that's that's nicely

10:35centered if I was to say

10:37fit the entire view that you can see that

10:38that's centered it goes to the middle so

10:40now if I go back

10:41to my dashboard I've got sheet 4 here and I

10:43'm just gonna this time float it out so I'm

10:45gonna float it

10:46out and you can see there it is I'm gonna

10:48hide the name of the sheet and I'm just

10:50gonna make this a

10:51lot smaller so it's much much better okay

10:54and you can do a few things like style this

10:56out so let's

10:57very very quickly give it a nice thick

10:59border so it's easy to see and we could

11:01even go into the

11:02view and for this specific worksheet if we

11:05just go into formatting let's just go into

11:08the formatting

11:10and can we do the worksheet shading here

11:12yeah let's just make this slightly gray and

11:15then that will

11:15sit nicely on our dashboard like that so

11:18there we have the reset my view and it's

11:20ready to go you can

11:21see the tool tip says day of smaller state

11:23third of January so now what we need to do

11:25is set another

11:26action so when I click on this sheet it

11:28passes it to the chart and changes this

11:31parameter so let's

11:32go back into dashboard actions add another

11:34parameter action and this time it's coming

11:37from

11:37this sheet which is going to be sheet 4

11:40okay and it's going to change the same date

11:43parameter we

11:44changed before it's going to grab this

11:46state and it's going to pass it to that

11:49parameter okay so

11:50let's hit okay and let's go ahead and try

11:53this out so if I then go ahead and hit this

11:55date you'll see

11:56that it resets now there's an issue there

11:58because essentially this blue item means it

12:00's selected

12:01and you can do a few hacks to make sure

12:02that this doesn't happen so once you see

12:04when I click it

12:05nothing happens because I was deselecting

12:07it only when I click it again does it

12:09actually sort of

12:10come back into action so you can actually

12:13set this up in a way that it doesn't sort

12:16of persist

12:17the selection if that makes sense but you

12:19can see that that works so now you can see

12:21the parameter

12:22here is 301 2017 and now if I click on one

12:25of these other dates you can see that that

12:28works fine

12:29go then reset my view and now we're back to

12:31the beginning okay so that's pretty much it

12:33that's

12:33how this is all working now that we've done

12:35that I can actually get rid of my date

12:37parameter and I

12:38can move this back over here to the top

12:40right just to make it sort of aesthetic and

12:42you can actually

12:43start to use this technique in lots of

12:45different ways one technique you might use

12:47it for is if you

12:48decide to build a selector with a start

12:51date and an end date you can give people

12:53two sort of

12:54options where they can choose their

12:55starting year and their finish year and it

12:57can actually filter

12:58both ends of this calculation essentially

13:00you're making sure that you put two filters

13:02in one which

13:03controls when the data starts and one which

13:05controls when the data ends and then

13:07everything

13:08that's left over is essentially in between

13:10and you basically have a dynamic way of

13:12choosing start and

13:13finish dates in a visualization so that's

13:15pretty much it there's not much more to it

13:17it's a really

13:18really simple trick you can use this in

13:20lots of different use cases but I thought I

13:22'd just make

13:23a video I was teaching ds25 at the data

13:25school in London today and they actually

13:28asked me this

13:29question and that's where this came from

13:32shout out to Jesus for reminding me that I

13:34didn't actually

13:35need to have the date value here inside of

13:37the text element so thank you for that

13:40because I just

13:42completely forgot that that wasn't

13:43necessary because it's already in our marks

13:45pane so it

13:46gets sent along anyway into our view now

13:49one last thing I'm going to do is disable

13:51these tool tips

13:52so if I go into the tool tips and I just do

13:54select the tool tips it's not going to show

13:56up with this

13:57date so now when I go back to my dashboard

13:59this doesn't need any sort of explanation

14:02if I just

14:02go in and reset my view it's going to do

14:04its job as it should do so thanks for

14:06watching that's pretty

14:07much it for today I just wanted to show you

14:09this really cool trick and just to see if

14:11there's maybe

14:11a use case that you can find for it and

14:13maybe something you might find useful I'm

14:15going to

14:16really struggle to name these use cases

14:17because I don't know how to tableau doesn't

14:19we don't have

14:20sort of a standardized way of naming these

14:22things so if you can think of a nice name

14:24to give this

14:25for the thumbnail maybe we can change it in

14:27the future let me know in the comments

14:28below but

14:29otherwise I'm just going to name it

14:30something like changing dates with the

14:32parameter action

14:32and leave it at that thanks for watching

14:34and I'll catch you in the next video