Parameter Actions – Page 9

My initial thoughts when I first saw this week’s challenge posted by Ann Jackson, was this would be pretty straightforward. And on the whole it was. I’ve dealt with defining specific date periods for comparison in the past, both with other #WorkoutWednesday challenges and within my job too. But Ann threw a curveball with her dynamic input requirement using what she termed as ‘custom buttons’.

The date listing didn’t phase me – the date forms part of the dataset, so displaying that to look like formatted ‘buttons’ (aka circle marks) was something I knew I could do.

It was the time range options that had me puzzled. The values ‘Last 3 Months’, ‘Last 6 Months’ etc aren’t values associated to any field in the dataset. Typically they would be options of a parameter. However parameters can’t be formatted in the way Ann presented. The closest you can get with a traditional parameter is a radio button list, where you have limited formatting options – certainly nothing as colourful as Ann’s requirement.

When I came to tackle this challenge, as this was the area I was most stumped over, I chose to just use the basic parameter concept to start with to get everything else working as I hoped. When I finally figured out how to do it, I adapted what I’d built to incorporate my change. For the purposes of this write up though, I’m going to start with the ‘dynamic inputs’.

Pick Time Range

From observing the PICK TIME RANGE selector in Ann’s published viz and interacting with it (hovering over the marks, clicking on an option etc) I deduced I needed to build a viz using Circle marks, and a row per option. The problem, as discussed above, was there was no dimension in the dataset that I could use to give me the values I wanted.

I needed to create one, but how?

By using a type of data densification technique, that I’ve used on other challenges, to essentially ‘fake’ a new field. My fellow #WorkoutWednesday pal, Rosario Gauna, recently co-authored a blog all about this technique, which you can read here. As a consequence I’m not going to go into too much detail about this.

I created the following calculated field

Pick Time Range

IF MONTH([Order Date]) = 1 THEN ‘LAST 3 MONTHS’
ELSEIF MONTH([Order Date]) = 2 THEN ‘LAST 6 MONTHS’
ELSEIF MONTH([Order Date]) = 3 THEN ‘LAST 9 MONTHS’
ELSEIF MONTH([Order Date]) = 4 THEN ‘LAST 12 MONTHS’
ELSE ‘YEAR TO DATE’
END

Given the dataset contains records for every month in the 4 years, I could guarantee there would be a record for each of these options.

I used the MIN(0) trick (or MIN(0.0) to create the display I was after, as this ensured I could align the text to the right of the circle mark I needed. This was plotted against my new Pick Time Range field (which has Show Header unchecked). Using MIN(0.0) meant I had a bit more precision when it came to fixing my axis to keep everything starting from the left when I eventually put on the dashboard. My axis were fixed from -0.01 to 0.1.

To make a value look like it is ‘selected’ did still require the use of a parameter….

Time Range

… and another calculated field

Time Selected

[Pick Time Range]=[Time Range]

This was then added to the Colour shelf, with the colours adjusted accordingly

Finally to make the colour change ‘on selection’, I needed a Parameter Action.

First I added the sheet created above to a dashboard. By default, this automatically displays the Time Range parameter and the colour legend to the dashboard. Leave these for now. I then selected Dashboard > Actions > Add Action > Change Parameter, and configured the options as below

When an option is clicked on, the Time Range parameter can be seen to change, and in turn, the selected option changes to pink.

The legend, and the Time Range parameter can now be removed from the display on the dashboard. This bit is done, onto the Date Selector 🙂

Pick End Month

Initially this follows similar principles to the above. I created a calculated field to store the month of the date

Order Date Month

DATE(DATETRUNC(‘month’,[Order Date]))

This sets every date to be the 1st of the associated month. I formatted this date to display as <month> <year>, and added these to a view in the same way I did the above, using MIN(0.0), a circle mark and, right aligning the label. The dates need to be sorted to descending.

Another parameter is needed to store the ‘selected’ end month, which I defaulted to 01 October 2019 (aka October 2019)

Date

But this time, we don’t just need to highlight the date selected, we need to colour the circles based on whether the date is within the ‘current period’, which in turn is based on the Time Range selected above, or within the ‘previous period’.

So if the end month is October 2019 and the Time Range is LAST 6 MONTHS, the Current period is the dates from May 2019 to October 2019, and the previous period is the 6 months prior to that.

To determine all this, I need to determine the start and end of the current period and the start and end of the previous period.

The end of the current period is simple, its the date selected

End Date Current Period

DATETRUNC(‘month’,DATE([Date]))

this is probably a bit overkill, as the default date is 1st October and all the other dates have been truncated to be the 1st of the month, but this just ‘makes sure’ 🙂

To get the end of the previous period, I need to either go back a number of months from the end date, or set to the same date of the previous year, if Year To Date is selected. I need to use a DATEADD function, but I need the number of months as an integer, so I need another calculated field to help with this, which will return me a number based on the value in the Time Range parameter

Time Value

CASE [Time Range]
WHEN ‘LAST 3 MONTHS’ THEN 3
WHEN ‘LAST 6 MONTHS’ THEN 6
WHEN ‘LAST 9 MONTHS’ THEN 9
WHEN ‘LAST 12 MONTHS’ THEN 12
ELSE 0
END

So now I have this, I can work out

End Date Previous Period

IF [Time Value] = 0 THEN
//take off a year
DATEADD(‘year’,-1,[End Date Current Period])
ELSE
//go back a few months
DATEADD(‘month’,([Time Value] * -1), [End Date Current Period])
END

and

Start Date Current Period

IF [Time Value] = 0 THEN
//1st of Jan for current year
DATETRUNC(‘year’,[End Date Current Period])
ELSE
//calc start time period as 1 month on from previous period end
DATEADD(‘month’, 1, [End Date Prior Period])
END

and

Start Date Prior Period

IF [Time Value] = 0 THEN
//1st of Jan for previous year
DATETRUNC(‘year’,[End Date Prior Period])
ELSE
//calc start time period (x months – 1) before end date
DATEADD(‘month’, ([Time Value]*-1)+1, [End Date Prior Period])
END

With all the dates now sorted, we can determine which colour each month should be set to

COLOUR:Date Selector

IF [Order Date Month]>=[Start Date Current Period] AND [Order Date Month]<=[End Date Current Period] THEN ‘blue’ ELSEIF [Order Date Month]>=[Start Date Prior Period] AND [Order Date Month]<=[End Date Prior Period] THEN ‘dark grey’
ELSE ‘grey’
END

which can then be added to the Colour shelf of my date list sheet.

To get the Date parameter to change on selection of the sheet, we need to use parameter actions again, so add the sheet to the dashboard, and create a new Dashboard Action

Now we’re ready to build the line chart…

Period by Period Line Chart

This is essentially a ‘Sales Over Time’ chart, but we can’t just plot a date on the columns shelf as we need to be able to compare the time periods, ie the value of sales associated to the date of the current start period must be plotted in the same ‘column’ as the value of the sales associated to the the date of the previous start period.

This is managed using the table calculation INDEX() to essentially number the dates from 1 to x for each period band (which I could use my COLOUR:Date Selector field to segregate). This concept of plotting dates from a set point is sometimes referred to as a rocket chart, although there are other names for them.

My Order Date Month field has to exist on the Detail shelf, and and the Index is set to compute using that field too

However, as you can see, I have 3 date periods – the dates based on the current period, the dates in the previous, and all other dates (the light grey line). We don’t want these, so another calculated field…

Dates To Include

([Order Date Month]>=[Start Date Prior Period] AND [Order Date Month]<=[End Date Prior Period]) OR ([Order Date Month]>=[Start Date Current Period] AND [Order Date Month]<=[End Date Current Period])

… is added to the Filter shelf and set to True (the dates to display must either be within the Current Period, or within the Previous Period).

Add this to the Dashboard, and you can now start to see how the line chart will change as the dates are changed and the time period are changed.

So final step now…

Title

The title of the dashboard updates based on the inputs selected, so the title is just built on another sheet, placing all the relevant fields on the Text shelf and formatting accordingly