How to use Parameter Actions in Tableau to Create a Dynamic Date Ranges | #WorkoutWednesday | 2019 | 42

Here we are for week 42 of #WorkoutWednesday with a truly wonderful challenge by Ann Jackson. What’s great about this challenge is that, at it’s core, it’s similar to a few other previous challenges but it builds on them a super cool new Tableau feature; Parameter actions.

Parameter actions are a new form of interactivity that allows the user to change the value of a parameter with a click. This opens up a whole new realm of interactivity. Take a moment and think of all the dashboards you’ve built that have parameters? And now, with these new actions, you can remove those dropdowns, radio buttons, etc. and replace them with more intuitive clicks.

This challenge and walkthrough is an introduction to this new functionality. After building, I hope you will have a better understanding of how parameter actions work and how you can implement them in your dashboards moving forward.

So as always, before we start, let’s take a look at what we’re going to build

And here are the requirements

Week_42__Can_you_build_a_comparative_line_chart_with_dynamic_inputs__–_Workout_Wednesday.png

Alright, let’s get into it

Now in most challenges, I’ll build the main visual first and then build the interactivity off that but I’m switching things up for my first parameter action challenge. I’m actually going to focus more on the filtering and interactivity which means we’ll start with the right side bar of the dashboard.

The first thing I’m going to do truncate the order date is the month date

## Month Date ##
DATEDATETRUNC( ‘month‘, [Order Date] ))

Now, while I’m here I’m going to go ahead and format this field as “Month Name Full Year” I can do that with custom format of “mmmm yyyy” in the default date format settings for this field.

I now that at some point, I’m gong to need a parameter based on this field so let’s create it now. It’s really simple to create because we simply “Add from Field”

Fullscreen_10_17_19__2_33_PM.png

If this were a “legacy” parameter challenge then I’d create a second parameter that I’d use to separate the different time frames. Guess what? With parameter action, you do the EXACT same thing; Create a parameter that looks like this

Edit_Parameter__PICK_TIME_RANGE__and_Tableau_-_Book2.png

Now that I have these two parameters, I’m ready to build the the calculation that will create the dynamic periods, keeping in mind I’ll handle the interactivity later.

I’m not going to write the entire calculation I’m just going to write the first portion of it and the rest if different conditions of the same calculation.

## Periods ##
CASE [PICK TIME FRAME_parameter]
WHEN 1 THEN
( IF [Month Date] > DATEADD( ‘month‘, -3, [MONTH DATE_parameter] )
     AND [Month Date] <= [MONTH DATE_parameter] 
         THEN “CURRENT PERIOD
ELSEIF [Month Date]<=DATEADD(‘month‘, -3, [MONTH DATE_Parameter] )
    AND [Month Date]>=DATEADD(‘month‘, -5, [MONTH DATE_Parameter] )
         THEN “PRIOR PERIOD
END)
WHEN 2 THEN

WHEN … THEN
END

So now that I have this calculation I’m ready to do some testing. And this bit of test will done by simply manipulating the parameters manually using the controls on the worksheet. Speaking of the worksheet, let’s build it now.

You can see that the month labels are on the right side of the dot. To do that, we need to put [Month Date] on rows, change our mark type to “Circle” and then put Month Date on the label shelf of marks card.

Tableau_-_Book2.png

Well that does look right. And even when we hide the row headers and align the labels to the right, still nothing…

Tableau_-_Book2.png

What we actually need is a continuous measure on columns. So I’ll employ my favorite placeholder field MIN(0).

Tableau_-_Book2.png

There we go! Now, let’s throw our [Periods] field on color…

2019-10-17 21-37-36.2019-10-17 21_38_31.gif

Yeah baby! Alright, while we’re here let’s test the first (easiest) parameter action. When I click on a [Month Date] I want to change the date value in the parameter. This is what the setup looks like

Edit_Parameter_Action_and_Actions_and_Tableau_-_Book2.png

And does it work? Keep your eye on parameter control…

2019-10-17 21-55-07.2019-10-17 21_56_09.gif

This is so awesome. And this is when it clicked for me. What is the significance of parameter actions?

Parameters, by design, exist outside the datasource and interact with your data from the outside-in. A parameter value does nothing until you create a calculated field to bring it into the datasource. Parameter actions allows for bi-directional interaction. Take a data source value, put it into a parameter value, create a calculation to bring it back in and manipulate your view.

Freaking GENIUS! Let’s roll!

Okay, now that I’ve just had my epiphany I’m ready to get figure out how the other parameter is going to work. I set it up as an integer 1-5 so I need a way to replicate what I did with months but with separate integers. I’ve seen some conversations in the community about using parameter actions to manipulate measure values. This is pretty significant because I can create extra measures very easily.

## LAST 3 MONTHS ##
1

Rinse & Repeat! And I can set up a identical view to the month selection view but using Measure Names & Values.

Tableau_-_Book2.png

You can see that the aggregation of the values are set to MIN(), the reason for that is because each value is stamped on each row of my data set and when I bring my value into, Tableau will try to sum those values and that’s not what we want.

Now, to get the color right, I need to put a two discrete fields on the color shelf. Measure Names is one and this other is a copy of my discrete [PICK TIME RANGE_parameter] field

## Time Frame Text ##
[PICK TIME FRAME_parameter]

Tableau_-_Book2.png

And now we just need rotate each parameter action and edit the colors each time. And then it’s time to test our parameter action

Edit_Parameter_Action_and_Actions_and_Tableau_-_Book2.png

Let’s see if it works…

2019-10-18 11-34-48.2019-10-18 11_35_21.gif

Awesome!

The last major component of this challenge is to create the line chart. And for the line chart, we need to match each month with their position in the given period. Meaning we want to line up the first month of each period and so on. This is BEGGING for an INDEX() table calculation function on columns.

I’ll put INDEX() on columns with sales on rows. Next I’ll put [Month Date] on the detail shelf and [Periods] on the color shelf. Then to set up my table calc let’s “say out loud” what we want to happen and refer to Andy Kriebel’s excellent tutorial on how to interpret table calculations as an english sentence

For each [Period], compute the row number by [Month Date].”

Table_Calculation.png

Tableau_-_Book2.png

Now I need to add the worksheet title. And in order to that, we need to fix the first and last months of each periods

## Current Min Month ##
MIN( { MIN( if CONTAINS( [Periods], “CURRENT” ) THEN [Month Date] END ) } )

Then you can rinse and repeat for each of the other months that you need. I added those fields to the detail shelf. Then you can edit the worksheet title to get the desired result.

Tableau_-_Book2.png

We are in the home stretch! Let’s get this thing together in a dashboard and duplicate the actions and…

2019-10-18 13-11-02.2019-10-18 13_12_23.gif

Excellent challenge!

Go Forth and Viz!

1 comment on “How to use Parameter Actions in Tableau to Create a Dynamic Date Ranges | #WorkoutWednesday | 2019 | 42”

  1. Pingback: How to use the DISTANCE function in Tableau | #WorkoutWednesday | 2019 | 43 – MY DATA MUSINGS

Leave A Reply

Your email address will not be published.