Tag: Parameter Actions

Can you compare state sales – Part 2?

Donna Coles3 Comments

This week’s #WOW challenge is an extension of this challenge which I blogged about here. The premise is to mimic a real world scenario – a dashboard has been built and used, but now, a few months after use, additional functionality is required. I am going to be building on top of the solution I delivered for week 11. If you participated in that week, your solution may not have matched mine, so this guide may not be as coherent.

Identifying the selected states

In part 1, we captured the single state selected within a parameter pSelectedState via a parameter dashboard action. We now need to be able to capture multiple states. We’re still going to use the same parameter and a parameter action, but we need to adapt what will be stored within in it.

Instead of a single state being stored in the parameter, we’re now going use this parameter to build up a delimited list of the states selected. We can then interrogate that string to determine the first and the second state selected, and identify if any additional states have been selected.

When using delimited strings, you need to choose a character to separate the field values that isn’t going to appear within the field value itself; ie choose a character that won’t appear within a State name. In this instance I’m choosing | (pipe), and I’m going to build up the parameter as follows

ActionpSelectedState
No states selected<empty string>
1 state selected eg Arizona|Arizona|
2 states selected eg Arizona then Utah|Arizona||Utah|
3 states selected eg Arizona, then Utah, then Colorado|Arizona||Utah||Colorado|
Existing state is selected again (trigger to reset the view)<empty string>

Based on the logic above, we need to modify the existing field

State for Param

IF CONTAINS([pSelectedState], [State]) THEN ” //selected state is already in the parameter, so reset back to ”
ELSE [pSelectedState] + ‘|’ + [State] + ‘|’ //append current state selected to the existing parameter string
END

To see how this is working, go the the dashboard and display the pSelectedState parameter. Click on states and see how the parameter is changing.

As we interact with the hex maps, we can see the list of states building up in the parameter. We’ve obviously lost some of the other functionality now, but that’s ok, we have more things to adapt.

We can now manipulate this string to identify the first two states selected

First Selected State

SPLIT([pSelectedState],’|’,2)

returns the string that comes before the 2nd instance of the ‘|’ (and after the 1st)

Second Selected State

SPLIT([pSelectedStateString],’|’,4)

returns the string that comes before the 4th instance of the ‘|’ (and after the 3rd).

We can then use these fields to set the colour for the map

State Colour

IF [State] = [First Selected State] THEN ‘First’
ELSEIF [State] = [Second Selected State] THEN ‘Second’
ELSE ‘Other’
END

Replace the Is Selected State field that is currently on the Colour shelf and the Shape shelf of the Column (2) marks card, with this new State Colour field. Ensure you have at least 2 states listed in the pSelectedState parameter, so you have options for First, Second, Other all listed in both the colour and shape legends.

Adjust the Shape legend and set the First and Second values to use the same hexagon shape used initially, but set the Other option to use a transparent shape.

Adjust the colours. Increase the opacity on this mark to 100% Set the colours as you wish – I used:

  • First – teal : #66b3c2
  • Second – light teal : #bce4d8
  • Other – pale grey : #d3d3d3

Click around on the dashboard. You should find as you click the first state it is coloured dark teal. Click a second, it is coloured light teal. Click either selected state again, and the map resets. Click more than 2 states, and only the first 2 are coloured, whilst the parameter continues to capture all the states being clicked.

Alerting if more than two states are selected

We will need a field to identify if more than 2 states have been selected.

More than 2 Values Selected?

SPLIT([pSelectedStateString], ‘|’, 6) <> ”

This is looking for a 3rd instance of a value within the delimited string, and returns true if there is one.

As this is a boolean field, we can use it to control the visibility of the message text box we want to display.

We also need to ensure that when the user clicks on the message, it disappears, and the hex map is re-set, ie the pSelectedState parameter gets set back to empty string. Another field will help us with this

State Reset

”

On a new sheet, add State Reset to Text. Then edit the text to the relevant message, and align centrally. Change the tooltip to say ‘click here to reset’. Set the background colour of the sheet to a pale grey. Name the sheet ‘Alert’ or similar.

On the dashboard, delete the contents of the pSelectedState parameter and remove it from the dashboard. Add the Alert sheet as a floating object. Set to fit entire view and remove the title. Add a border to the object.

Select the object, then in the Layout pane, set the Control visibility using value option to the More than 2 Values Selected field.

Then add a dashboard parameter action to reset the parameter when the message is clicked

Reset States

On select of the Alert object, set the pSelectedState parameter to the value stored in the State Reset field.

Click around, selecting more that 2 states and test the behaviour. Now we can move on the adapting the other charts.

Adapting the calculations

To sense check what is going on, on a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter. Add State Label to Rows, and show the pSelectedState parameter.

When only 1 state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ . When 2 states have been selected we just want the state names against the 2 relevant rows. Otherwise we want NULL to show. We need to adapt the State Label field as follows

State Label

IF [First Selected State] <> ” AND [Second Selected State] = ” THEN
IF [State] = [First Selected State] THEN [State] ELSE ‘Other States (Avg)’ END
ELSEIF [First Selected State] <> ” AND [Second Selected State] <> ” THEN
IF [First Selected State] = [State] OR [Second Selected State] = [State] THEN [State] END
END

Now we need to display a different value for the sales measure depending on whether we have 1 or 2 states selected. So we need to adapt the existing Sales To Display field

Sales To Display

IF CONTAINS([pSelectedState], MIN([State Label])) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the parameter string contains the State Label value, then use the sales, otherwise average the sales over the number of states that make up the sales. Format this to $ with 0dp.

Add this to the table, and remove State from the display

Add State Label to the Filter shelf and exclude NULL. Now remove the second state from the parameter and check the result

Adapting the bar chart

On the existing bar chart sheet, verify the pSelectedState parameter is displayed. Replace Is Selected State on the Colour shelf with the State Colour field and remove Is Selected State from the Rows shelf.

Add another state to compare against. Exclude the NULL values that display (add State Label to Filter and exclude Nulls)

We always want to make sure that the first state selected is listed first.

Sort- State Label

IF [State Label] = [First Selected State] THEN 1
ELSEIF [State Label] = [Second Selected State] THEN 2
ELSE 3
END

Add this field on to Rows as a discrete (blue) pill, between the existing two pills and then hide it.

Adapting the line chart

On the existing line chart sheet, verify the pSelectedState parameter is displayed

Add State Label to Filter and exclude null. Replace Is Selected State on the Colour shelf with the State Colour field.

Displaying the additional charts on click.

The display of the bar and line chart are based on the dynamic zone visibility functionality, and the field Show Viz (if you have downloaded my original solution workbook to build onto, you may need to unhide fields in the data pane for this field to be visible).

Amend Show Viz to

[First Selected State] <> ”

Amending the Chart Title sheet

On the Title sheet, replace the Is Selected State on the Filter shelf with Show Viz = TRUE.

Remove State Label from the Text shelf and add First Selected State and Second Selected State to Text. Additionally create a calculated field

Label:All State

IF [Second Selected State] = ” THEN ‘Other States (Avg)’ ELSE ” END

Add this to Text too.

Adjust the text as below and apply matching colour coding to the fonts. Align the text centrally.

Finally update the title and instructions on the dashboard.

My published viz is here.

Happy vizzin’!

Donna

Filtering and Highlighting

Donna Coles1 Comment

It was Sean’s turn to set the challenge this week which included a lots of little details to provide a wealth of different ‘views’ on the same data within a single chart, specifically the change of measure and the change of date granularity.

Building a viz showing the timeline at different levels of granularity based on user selection, is something I’ve done several times in the past, both for other WOW challenges and also in real work situations. So I immediately headed down my ‘usual route’, creating a parameter to identify the level of the date to report at, and a calculated field to display the appropriate level based on the parameter. However, then I noticed that in Sean’s viz, if I changed the date level, the date axis display also changed, sometimes showing <Month Year> format, sometimes <Year Q> etc. Having the axis change like this isn’t something that would work with my ‘usual’ solution. I did ponder this for some time, and the only thought I had was multiple sheets – one for each date part. Sean hadn’t stated how many sheets were needed for the challenge, and it made me wonder if the omission was perhaps deliberate…

Building the Month chart

To start we need to define a parameter to identify the level of date to select

pDateGranularity

string parameter defaulted to ‘month’ with a list of options, which have an alternative display name

We also need to identify the measure we want to show. Before we can define this, we need

Profit Ratio

SUM([Profit])/SUM([Sales])

and

#Orders

COUNTD([Order ID])

We also need to be able to capture the selected measure in a parameter

pMeasure

string parameter, defaulted to PROFIT (note case). A list isn’t needed as the value will be set via an action on the dashboard

so we can then build

Measure to Display

CASE [pMeasure]
WHEN ‘SALES’ THEN SUM([Sales])
WHEN ‘PROFIT’ THEN SUM([Profit])
WHEN ‘PROFIT RATIO’ THEN [Profit Ratio]
WHEN ‘ORDERS’ THEN [#Orders]
END

On a new sheet, add Order Date to Filters and set as a relative date filter to the last 4 years. Add Order Date to Columns as a continuous (green) pill set at the month level, and add Measure to Display to Rows.

Add the Order Date filter to ‘context’ (right click > Add to context) and set it to apply to worksheets > all using this data source. The context is necessary as when it comes to the option to select a date to highlight, only the dates within the timeframe selected should be visible. Show the pMeasure and pDateGranularity parameters.

Edit the Measure to Display axis to change the title to reference the pMeasure parameter instead (right click and edit axis).

Test the values change as expected by changing the text in the pMeasure parameter to SALES or ORDERS or PROFIT RATIO.

To identify a month to highlight, I created

Order Date – month

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

and formatted this to a custom format of yyyy-mm

I then created a set off of this field (right click > create > set) and selected a single date 2021-11

Order Date – month Set

In order to make use of the set and its values, the set needs to be on the view. Add Order Date – month Set to the filter shelf. If it doesn’t happen by default, change the option on the pill to Show In/Out of set and then select both In and Out as the filter options (ie all possible values so nothing is actually being filtered).

Then click on the Order Date – month Set pill on the filter shelf and select show set. The list of values in the set should display.

Change the input type of the Set control to be a single value drop down, where the (All) option doesn’t show (via customise) and the option All values in Context is selected

We need to capture the value associated with the date selected

Month Measure to highlight

IF ATTR([Order Date – month Set]) THEN [Measure to Display] END

Add this to the Rows shelf, then make the chart dual axis and synchronise the axis. Adjust the colours of the Measure Names and then make the mark type of the Month Measure to Highlight marks card, a circle, and increase the size.

Hide the right hand axis, remove the title of the bottom axis and remove all row & column dividers. Right click on the nulls indicator and hide indicator.

Call this sheet month chart or similar.

Building the Quarter chart

Go through similar steps to build a chart to display the information at a quarterly level. The field on the Columns shelf should be Order Date set at the continuous (green) quarter level.

You will need a field

Order Date – quarter

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

and format this to a custom format of yyyy-“Q”q

Use this to create a set off of this field (right click > create > set) and Order Date – quarter Set

You’ll also need

Quarter Measure to highlight

IF ATTR([Order Date – quarter Set]) THEN [Measure to Display] END

Building the week chart

Repeat again.

The field on the Columns shelf should be Order Date set at the continuous (green) week level.

You will need a field

Order Date – week

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

and format this to a custom format of yyyy-“W”ww

Use this to create a set off of this field (right click > create > set) and Order Date – week Set

You’ll also need

Week Measure to highlight

IF ATTR([Order Date – week Set]) THEN [Measure to Display] END

Creating the BANs

To start with, we need to create some calculated fields to store the total values for each measure and the ‘highlighted’ vallues

Total Sales

{FIXED: SUM([Sales])}

format to $ with 0 dp

Total Profit

{FIXED: SUM([Profit])}

format to $ with 0dp

Total Profit Ratio

{FIXED: [Profit Ratio]}

format to % with 0 dp

Total Orders

{FIXED:COUNTD([Order ID])}

format to number with 0 dp

and we’ll also need values for the highlighted date

Highlighted Sales

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Sales])
END

format to $ with 0dp

Highlighted Profit

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Profit])
END

format to $ with 0 dp

Highlighed Profit Ratio

SUM([Highlighted Profit])/SUM([Highlighted Sales])

format to % with 0 dp

Highlighted Orders

COUNTD(IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN [Order ID]
END)

format to number with 0 dp.

On a new sheet, double click into columns and manually type MIN(0.0). Repeat this 3 more times, so there are 4 instances of MIN(0.0) on the Columns shelf.

On the All marks card, add Measure Names to the Label shelf.

Now right click on Measure Names in the left hand data pane and select Aliases. Alias each of the MIN(0.0) measures to SALES, PROFIT, PROFIT RATIO, ORDERS

The labels on the viz should change

Create a new field

Sales Selected

[pMeasure] = ‘SALES’

On the 1st MIN(0.0) marks card, change the mark type to shape and add Sales Selected to the shape shelf. Use a transparent shape (see here for details) for the False value, and use the provided sparkline image (which you need to add to your shape palette) for the True value (change the pMeasure parameter to SALES for the True option to show). Increase the Size of the shape to around the 3/4 mark.

Add Total Sales and Highlighted Sales to the Label shelf, and then adjust the font position, size and colour accordingly. This is what my label dialog looked like – Measure Names is centred, while the other two fields are right aligned, and the overall alignment is right too.

Adjust the MIN(0.0) axis to be fixed from -0.2 to 1 to give the mark and the text enough space to breathe.

Now repeat the exercise for the other 3 marks cards. You will need the fields

Profit Selected

[pMeasure] = ‘PROFIT’

Profit Ratio Selected

[pMeasure] = ‘PROFIT RATIO’

Orders Selected

[pMeasure] = ‘ORDERS’

and you’ll need to change the value in the pMeasure parameter to get the relevant shape to show or not for each measure

Finally, create fields

True

TRUE

False

FALSE

and add these both to the Detail shelf of the All marks card. We’ll use these to stop the BANs from being ‘highlighted’ on click on the dashboard.

Then remove all row/column dividers and gridlines & zero lines

Building the dashboard

Create the dashboard, and arrange all the objects so the controls/filters are listed in a row at the top, and the 3 charts are arranged side by side underneath.

If you’ve got dates selected for your quarter & week, then in the data pane, right click on the relevant set, edit set and uncheck any option. This should then give you (None) in the dropdowns.

We will use dynamic zone visibility to control which charts & highlight date controls to display. For this we need fields

Show Monthly

[pDateGranularity] = ‘month’

Show Quarterly

[pDateGranularity] = ‘quarter’

Show Weekly

[pDateGranularity] = ‘week’

On the dashboard, select the monthly chart, and then from the Layout tab, select Control visibility using value and choose the Show Monthly field. Set the control visibility for the highlight a month object to also use the Show Monthly field.

Set the control visibility for the Quarterly chart and highlight a quarter object to use the Show Quarterly field, and set the control visibility for the Weekly chart and highlight a week object to use the Show Weekly field.

Create a dashboard parameter action to set the pMeasure parameter on click of the BANs sheet, passing in the Measure Names to the parameter

Set measure

Create a dashboard filter action to stop the BANs from being highlighted ‘on click’

Deselect BANs

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you combine relative and custom date filters?

Donna Coles1 Comment

For this week’s challenge, Kyle revisited a previous challenge from 2020, but remade it over utilising some of the newer features of Tableau, specifically Dynamic Zone Visibility. I blogged my solution to the original challenge here, so this blog will lift some of the techniques (and documentation) I employed directly.

Define the parameters

The first step in this challenge is to define all the parameters needed, these being :

pDateSelector

A string parameter just set to contain the value Last 30 Days

This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.

pDays

An integer parameter defaulted to 120

pStartDate

A date parameter defaulted to 01 Jan 2023

pEndDate

Another date parameter defaulted to 01 Aug 2023

The chart needs to adjust based on a measure selected, so we need to capture the measure option selected

pSelectedMeasure

string parameter defaulted to Sales

This parameter will be set by a parameter action, so again there is no need to actually list the possible values.

Finally, I also created a parameter

pToday

Date parameter defaulted to 02 May 2023.

The requirements indicate the information displayed should be restricted to ‘today’, using the TODAY() function. However given the data set is static, if I use TODAY() and look at this in a year’s time, nothing will show. So instead I have ‘hardcoded’ ‘today’ using this parameter.

Using this parameter I then created

Order Date < Today

[Order Date] <= [pToday]

and added this as a data source filter set to True. This ensures that all the sheets I build is then automatically ignoring and data where the Order Date is 3rd May 2023 onwards.

Building the time-series chart

We need to determine which measure to show based on the selection made by the user and stored in the pSelectedMeasure parameter. This will be Sales, Profit or a count of the number of orders. First we need to create

Count Orders

COUNTD([Order ID])

and then we can create

Measure to Display

CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Orders’ THEN [Count Orders]
END

Add Order Date set to the continuous day level (green pill) to Columns and Measure to Display to Rows. This gives us all data from the earliest day in the data set up to 2nd May 2023.

We need to restrict this based on the ‘date selector’, so create

In Timeframe

CASE [pDateSelector]
WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[pToday])
WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[pDays],[pToday])
ELSE [Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate]
END

If ‘last 30 days’, get the data that is greater than 30 days ago; if ‘last n days’, get the data that is greater than the last ‘n’ days ago, otherwise get the data between the start & end dates specified.

Add this to the Filter shelf and set to True.

Add another instance of Measure to Display to Rows, make dual axis and synchronise axes. Change the mark type of the first marks card to be Area and set the other to explicitly be a line.

Add pSelectedMeasure to the Colour shelf of the All marks card. Adjust the colour of the ‘Sales’ value accordingly.

If you’re not already displaying it, show the pSelectedMeasure parameter input, and manually change the value by typing in ‘Profit’. The chart will change colour, so again adjust accordingly. Repeat the process by typing in ‘Orders’.

Set the parameter back to ‘Sales’.

The tooltip needs to display different formatted values, so we need a couple of fields to handle this

Tooltip- Sales or Profit

IF [pSelectedMeasure] <> ‘Orders’ THEN [Measure to Display] END

Format this to $ with 0 dp, and set to use ( ) when value is negative.

Tooltip – Orders

IF [pSelectedMeasure] = ‘Orders’ THEN [Measure to Display] END

Format this to a number with 0 dp.

Add both these fields to the Tooltip shelf of the All marks card, and adjust the tooltip accordingly, positioning the two ‘Tooltip’ fields directly adjacent to each other

Hide the right hand axis (right click, uncheck show header), and edit the left hand axis to set the axis title to be sourced from the pSelectedMeasure parameter

Remove all gridlines, zero line, axis ticks etc and row/column dividers, and edit the date axis to remove the axis title. Name the sheet ‘Chart’ or similar.

Building the BANs

  1. Add Measure Names to Columns
  2. Add Measure Names and Measure Values to the Text shelf
  3. Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
  4. Reorder the columns to match the requirement

  1. (Optional) Change the mark type to shape and set the shape to be a transparent shape (see this blog post for more details)
  2. Change the formatting of the Measure Names and Measure Values on the Text shelf to set the size of the font to suit, and align middle, centre
  3. Format the Sales and Profit measures to be $ with 0 dp and ( ) for neg values.
  4. Add Measure Names to the Colour shelf and adjust colours to match the ones used on the line chart. (If you’ve used the transparent shape, edit the label to set the font to match mark colour).
  5. Format the display to remove the row lines
  6. Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
  7. Add In Timeframe = True to the Filter shelf to restrict the data.
  8. Uncheck the show tooltip option from the Tooltip shelf to stop the tooltip from displaying.
  9. Name the sheet ‘BANs’ or similar

Building the Date Selector

On a new sheet, double click into the Columns shelf and type in MIN(0.0) Change the mark type to Shape.Create a new field

Label: Last 30 Days

‘Last 30 Days’

And add this to the Label shelf.

Create a new field

Is Selected Date Option – Last 30 Day

[pDateSelector] = [Label:Last 30 Days]

And add this to the Shape shelf. Adjust the shape for the ‘True’ value to be a filled circle.

Show the pDateSelector parameter and change the text in some way. This should change the ‘shape’ to ‘False’. Set this shape value to be an open circle.

Change the colour to be a dark grey, and increase the font size. Uncheck ‘Show Tooltip’.

Now type in another instance of MIN(0.0) to Columns. Create the following fields

Label:Last N Days

‘Last N Days’

and

Is Selected Date Option – Last N Days

[pDateSelector] = [Label:Last N Days]

ON the 2nd MIN(0.0) marks card, replace the fields on the shape and label shelves with these ones.

By default, the ‘last n days’ shape should be set to false, so make sure it’s an open circle. Change the value in the pDateSelector parameter to ‘Last N Days’ and the shape should now be ‘true’ – set to a closed circle.

Create a 3rd instance of MIN(0.0) on Columns and create

Label:Custom Dates

‘Custom Dates’

and

Is Selected Date Option – Custom Dates

[pDateSelector] = [Label:Custom Dates]

and repeat the process above

Edit the axis of each to be fixed from -0.2 to 2. This has the effect of ‘left aligning’ the marks.

Hide the axis, and remove the row & column dividers, all gridlines and zero lines and axis lines.

Add Measure Names to the Detail shelf of the All marks card. The right click on Measure Names in the left hand data pane and select Alias. In the dialog box that presents, alias the 3 MIN(0.0) fields as per the 3 date selector options.

This step is the key to enabling the parameter action that will be set up to pass the appropriate ‘value’ into the pDateSelector parameter

Finally format the background of the whole worksheet to be grey.

Controlling the visibility of the selections

On the dashboard, I used a vertical layout container to add my title, the BANs and the Chart. Between the title and BANs, I added a horizontal container. On the left side of that I added the Date Selector sheet. On the right I then had another vertical container which contained other containers to display the start & end data parameters. It took a bit of ‘twiddling’ to get everything where I wanted.

I added the pDays parameter as a floating object and positioned it below the Last N Days option. I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Last N Days calculated field.

Similarly, for the date input fields, I had the section all within a single container, so I set the visibility at the container level, rather than the object level (although you could repeat the step against all the objects you need to hide individually). For the container, I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Custom Dates calculated field.

Adding the interactivity

To change the timeframe displayed in the BANs and chart, create a dashboard parameter action

Set Date Selection

On select of the Date Selector sheet, target the pDateSelector parameter, passing though Measure Names. When the selection is cleared, reset to ‘Last 30 Days’.

To change the measure displayed in the chart, create a dashboard parameter action

Select Measure

On select of the BANs sheet, target the pSelectedMeasurew parameter, passing through Measure Names. When the selection is cleared, reset to ‘Sales’.

Prevent the BANs and Selected Date option from being selected/highlighted

By default, clicking on one of the BAN numbers, or selecting an option in the date selector, will leave the option chosen ‘highlighted’ or ‘selected’ while the other options are ‘faded out’. To

Create two calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of the BANs sheet and the All marks card on the Date Selector sheet.

Then on the dashboard, create a dashboard filter action

Deselect BANs

On select of the BANs sheet on the dashboard, target the BANs sheet directly, passing through selected fields where True = False. Show all Values when the selection is cleared.

Repeat the exact process for the Date Selector sheet, creating a dashboard filter action called Deselect Date Selector.

You should now have a complete dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you build a dashboard using containers?

Donna Coles1 Comment

For this week’s challenge, Kyle got us to look at dashboard layout, specially using containers to arrange the charts and KPIs. He added in a sprinkling of interactivity to make the challenge more complete.

The charts aren’t overly complex, so I won’t go into too much detail on building them all out. I used the latest version of Superstore, v2023.1.

Building the KPIs

When I first did this, I built the KPIs on a single sheet, then realised that wouldn’t work to get the layout required, so I ended up with 3 sheets.

  • Rename Sales to SALES and format to $ with 0 dp.
  • Add Measure Names to Filter and select SALES only.
  • Add Measure Names and Measure Values to the Text shelf.
  • Align centrally and format the text (I used font size 12pt and 20pt).
  • Add Category to the Filter shelf, and select all. Set the filter to apply to worksheets > all using this data source
  • Add Order Date to Filter and select Range of Dates. The Order Date pill will be ‘green’. Click on the context menu of the pill and select the Month (May 2015) option, so the range of dates will change at monthly intervals rather than daily. Set the filter to apply to worksheets > all using this data source
  • Create a field called True containing the value TRUE and False containing the value FALSE and add both these fields to the Detail shelf. These will be needed later to stop the sheet from remaining highlighted on selection.
  • Stop the Tooltip from displaying.
  • Name the sheet Sales

Repeat the steps for the Profit Ratio measure – if this doesn’t exist, create the field

PROFIT RATIO

SUM([Profit])/SUM([Sales])

and format this to % with 1 dp.

For the Orders measure create

ORDERS

COUNTD([Order ID])

If need be, you can simply duplicate the Sales sheet and just change the Measure Names filter to the appropriate measure.

Creating the Line Chart

Create a parameter to store the name of the selected measure.

pSelectedKPI

string parameter defaulted to the word ‘ORDERS’

Create a field to store the measure to display based on the value of the parameter

Measure to Display

CASE [pSelectedKPI]
WHEN ‘ORDERS’ THEN [ORDERS]
WHEN ‘SALES’ THEN SUM([SALES])
WHEN ‘PROFIT RATIO’ THEN [PROFIT RATIO]
END

On a new sheet, add Order Date to Columns and set to be a green (continuous) month level and Measure to Display to Rows.

I wanted to make my tooltips and labels reflect the measure selected (this isn’t actually part of the challenge). I created

Label – Orders

IF [pSelectedKPI] = ‘ORDERS’ THEN [ORDERS] END

format this to a number with 0 dp.

Label – Profit Ratio

IF [pSelectedKPI] = ‘PROFIT RATIO’ THEN [PROFIT RATIO] END

format this to % with 1 dp

Label – Sales

IF [pSelectedKPI] = ‘SALES’ THEN SUM([SALES]) END

format this to $ with 0 dp.

Add all 3 fields to the Tooltip shelf.

Also create

Chart Label

PROPER([pSelectedKPI])

this is a new function introduced in v2023.1 and will convert ‘ORDERS’ to ‘Orders’ and ‘PROFIT RATIO’ to ‘Profit Ratio’ etc. Add this to Tooltip too.

Modify the Tooltip as below – the 3 Label fields should be directly side by side with no spacing. Only one field will have a value at any time.

Remove the titles from the axes, and remove all gridlines. Name the sheet Line.

Building the Bar Chart

Add Category to Rows and Measure to Display to Columns. Sort descending. Add the 3 Label fields to the Label shelf, and arrange side by side.

Add Chart Label to Tooltip and adjust the tooltip.

Hide the axes, remove all gridlines/axis rulers etc and hide the Category label. Adjust the formatting of the fonts as required. Name the sheet Bar.

Building the dashboard

Describing using layout containers can be quite tricky – objects move around as you place things in. I’m going to do my best to describe the set up/structure I have and hope that it gets you what you need.

My preference is to always start with a floating container sized as per the dashboard. I then add tiled objects into that. I also have a habit of trying to rename my containers in the navigation layout pane to help me find the right section.

So let’s start. Create a new dashboard and set it to to 1200 x 900px.

Click the Floating button at the bottom of the left hand pane, and add a Horizontal container. Set the x & y position to be 0,0 and the width to 1200px and height to 900px. Rename the container to Base.

From the Objects list on the Dashboard pane, click Tiled and add a Text object. Enter the text for the dashboard title. Add a blank object beneath the text object.

When working with containers, it’s always good to add blank objects as a ‘starting point’ These all then get removed.

Now add a vertical container between the Title and Blank object. Name this container Main Body. Add a blank object into that container.

Add a vertical container to the left of the blank object in the Main Body container. Name this container Left Nav. Add a Text object into the Left Nav container and enter the instructional text. Add a blank object above the instructional text.

Add another vertical container to the right of the Left Nav container within the Main Body container. Add the Bar and the Line chart into this container, one above the other. Call this container Charts.

At this point a Tiled container object will have been automatically added containing the parameter.

Leave this for now – we’ll address this shortly.

We can remove some of the blanks now too. Remove the blank within the Main Body container, that is to the right of the Charts container. You can select the object on the item hierarchy layout, right click and remove from Dashboard.

You can also remove the blank object at the bottom of the Base container, below the Main Body container. Your item hierarchy should look something like

Now add another Horizontal container to the charts container, above the bar chart. Add the 3 KPI sheets into this container, position side by side. Name the container KPIs. Select the KPIs container, and use the context menu to Distribute Contents Evenly.

If they haven’t already appeared, the select one of the charts/KPIs and from the context menu select Filters > Category and then Filters > Month of Order Date to get the filter controls visible on the dashboard. They should appear on the right hand side, within that Tiled container (underneath the viz).

From the item hierarchy section, expand the Tiled container until you find the controls listed (see how many containers that got automatically added!).

Select the Category filter (easiest to do this by clicking on the object in the item hierarchy), and then move that object and position it above the blank object in the Left Nav container. Then delete the blank object. Select the Month of Order Date filer object and do the same.

Now we’ve got everything we want , we can remove the Tiled container and all of the objects it contains from the dashboard. Just right click on the first Tiled container in the item hierarchy and Remove From Dashboard (say yes/ok to any prompt that appears). You should have something that looks a bit like this – not the item hierarchy layout.

Now you’ve got everything needed on the dashboard in the right containers, we need to tidy it all up.

  • Fix the width of the Left Nav container to around 205 px.
  • Change the Category filter to single value dropdown, defaulted to All
  • Fix the height of the KPIs container to around 175 px
  • Remove the titles from all the KPI objects, and ensure all set to fit entire view
  • Amend the titles of the bar and line charts to reference the Chart Label field.
  • Fix the height of the bar chart to around 340px and set to fit entire view.

  • Set the background colour of the whole dashboard to grey (Format menu -> Dashboard – Dashboard Shading)
  • Set the background colour of the bar and line chart to White and add a grey border (slightly darker than the background colour)
  • Add borders round the 3 KPI charts too.
  • Set padding around all the objects – I tend to use both inner and outer padding. The key is consistency to ensure the spaces between the objects are the same. I typically start with 10 px outer padding all round, and then adjust as required. Sometimes you may add padding to the container and not to the objects themselves, other times you may set the container padding to 0 and apply to the objects, or a combination of both.

Adding the Interactivity

To set the category bar chart to work as a filter, simply select the object and from the context menu select use as filter. Then go to the Actions list (Dashboard > Actions) and edit the ‘Filter 1 (generated)’ action and rename it to something more useful eg Filter bu Category.

For the bar & line chart to update ‘on click’ of a KPI, add a parameter action

Select KPI

On selection of the Orders, Profit Ratio or Sales sheet, set the pSelected KPI parameter, passing the value from the Measure Names field. Keep current value when selection is cleared.

Finally to prevent the KPIs from remaining highlighted in blue on selection, add 3 filter actions (1 per KPI) set up as follows

Deselect Sales KPI

On selection of the Sales sheet on the dashboard, target the Sales sheet directly, and set the fields as Source = True to Target = False.

Finally, to collapse the left hand nav section, select the Left Nav container and from the context menu, select add Show/hide button. A X button will appear which is floating by default. Move this to where you choose (you might need to add some additional left padding to the title to make space.

Load the dashboard in presentation mode to easily test the hide/show functionality.

And hopefully that should be it! Phew!

My published viz is here.

Once you’ve grasped the concept of containers, they really are the best way of controlling the behaviour and layout of objects on your dashboard. When I’m building something formal, I personally never want to have a Tiled container on my dashboard – this is an object that gets automatically added, and you can see from above, how many nested containers it ended up adding through a single action I took. If you’re not careful, you can end up with such a nest of containers, that it can get really hard to unpick.

Happy vizzin’!

Donna

Can You Compare State Sales? Part 1

Donna Coles2 Comments

This week’s #WOW challenge was set by me and born out of a client requirement to compare store locations via selections from a map. I adapted it to use our favourite Superstore dataset (v2022.4).

The core requirement is to be able to make a selection on the map and see how sales compare to the average of all the other states. There is more to this challenge, but it was too much for one week, so I’ve broken it into 2 parts. This is part 1. In a few weeks time, I’ll be building on this solution for a part 2.

Building the basic hex map

The requirements provide a link to here to get the relevant files needed to complete this challenge and build the hex map – this includes the 2022.4 version of Superstore, the Hex map template, the hexagon shape file and a transparent shape file (more on that one later).

Using the hex map template sheet provided, relate the Orders Superstore data to the hex map sheet, relating State/Province to State.

Then on a new sheet add Column to Column, Row to Row and State to Detail. Edit the Row axis, and reverse the scale.

Change the shape of the mark to be a hexagon (use the provided shape if need be and add to a custom shape palette), and increase the size of the marks. Add Sales to Colour and change to use the Grey sequential colour palette, and adjust the opacity to 80%. Add Country/Region to the Filter shelf and select United States. This will remove Alaska and Hawaii that don’t have any sales and aren’t in the Superstore data set.

Add Abbreviation to the Label shelf and align centrally, Adjust the font size if need be.

Identifying the selected state

We need to be able to capture the state that has been selected ‘on click’. This will be driven by a dashboard action.

When I first built this concept for a client, the natural first step was to utilise sets and set actions; that is capture the selected state in a set, and then colour the map, build the other charts and logic based on the existence in the set. However this method does caused some issues when I tried to prevent the highlighting later, so I chose to use a parameter and parameter actions instead.

Firstly we need a parameter that will be used to capture the state selected ‘on click’

pSelectedState

string parameter defaulted to <empty string>

We can then create

Is Selected State

[State] = [pSelectedState]

As we want to retain the colour by sales on the existing map, we need to make a dual axis. Show the pSelectedState parameter and type in ‘Florida’.

Add another instance of Column to Columns. Remove the Abbreviation label from the second marks card, and replace the Sales field on the Colour shelf with the Is Selected State field. Increase the opacity on this mark to 100% Set the colours as follows :

  • True- teal : #66b3c2
  • False- pale grey : #d3d3d3

Additionally, add Is Selected State to the Shape shelf of the second marks card. Set the True value to use the same hexagon shape, but set the False option to use a transparent shape (a transparent shape file is provided in the g-drive, and needs to be added as a custom shape).

This should make all the other states look like they disappear.

Make the chart dual axis, and synchronise the axis.

Format the Sales to be $ with 0dp, then add to the Tooltip shelf of the second marks card and adjust the tooltip. Remove all gridlines/row & column dividers and hide the axes.

Adding the Interactivity

Create a dashboard sized 1000 x 600.

Add a vertical container and add a text field to create the title. Below the title, add a horizontal container. Add the hexmap sheet into the horizontal container, then add a blank object to the right of it. Remove any legends etc that automatically get added. Set the width of the hexmap object to be fixed to 600 px

and set the height of the horizontal container the hex map sits within to 445px

Show the colour legend and set it to floating, and position bottom left.

Add a parameter action to set the selected state on click

Set Selected State

On selection of the Hexmap sheet, pass the State field into the pSelectedState parameter. Set the value to <empty string> when the selection is cleared.

If we click around on the dashboard page, we can see the colours being set, but everything is fading out on selection. To prevent this, create 2 calculated fields

True

TRUE

False

FALSE

Add these to the Detail shelf of the All Marks card of the hex map sheet. Then back on the dashboard, add a filter dashboard action

Deselect Map Marks

On select of the hex map object on the dashboard, target the hex map sheet itself, setting selected fields such that True = False. Show all values where selection cleared.

Now if we click on the dashboard, the shapes shouldn’t fade into the background. However we can’t ‘unselect’ the state and get back to its original state. This is because the filter action we added to stop the marks from fading actually unselected the mark, so when we click again, we’re not undoing any selection.

To resolve this, we need another calculated field.

State for Param

IF [State] = [pSelectedState] THEN ”
ELSE [State] END

If the state being clicked is the one already captured in the parameter, then set the field to <empty string>, else set the fields to the state being clicked.

Add this to the Detail shelf of the All marks card on the hexmap sheet, and then update the Set Selected State parameter action to pass the State For Param field into the parameter instead of the original State field..

Now when the map is first loaded, the State For Param field contains the name of the state, so that is passed into the parameter on click. As the parameter now has a value, the State For Param field changes for the selected state to be <empty string>, so if the same state is clicked, <empty string> is then passed into the parameter and the view resets.

Building the calculations for the other charts

On a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter.

When a state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ .

State Label

IF [Is Selected State] THEN [State] ELSE ‘Other States (Avg)’ END

Now we need to display a different value for the sales measure depending on whether its the selected state or not.

Sales To Display

IF ATTR([Is Selected State]) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the state is the selected one, then use the sales, otherwise average the total sales over the number of states that make up the sales. Format this to $ with 0dp.

Note – in hindsight, this could have just been SUM([Sales])/COUNTD([State]) even for the single state, as since the count of state will be 1, this would just equate to the SUM([Sales]) itself.

Add this to the table, and remove State from the display

Building the bar chart

Duplicate this sheet (as we want to retain the filters), then move Sales To Display to Columns, and add Category to Rows in front of State Label. Add Is Selected State to the Colour shelf.

To ensure the Selected State is always listed first, even if alphabetically it comes after ‘Other States (Avg)’, add Is Selected State to the Rows shelf between Category and State Label. Manually sort it so True is always listed before False, then hide the column (uncheck show header).

Reduce the Size of the bars, remove gridlines and column dividers. Lighten the row dividers. Adjust font sizes. Hide the column labels (hide field labels for rows) and hide the axis.

Remove Sales from the Text shelf, and check Show mark labels instead. Update the Tooltip.

Building the line chart

On a new sheet add Country/Region to Filter and set to United States. Add Order Date to Columns and set to the continuous month level (eg May 2021). Add Sales To Display to Rows, State Label to Detail and Is Selected State to Colour. Manually move the values in the colour legend so that True is listed first. Adjust the tooltip

Remove the axis titles, adjust the axis fonts. Remove row/column dividers and zero lines and axis rulers.

Putting it all together

On the dashboard, add a vertical container between the hexmap and the blank object. Add the line chart and bar chart on top of each other. Remove the title for the bar chart, and update the title of the line chart to reference the pSelectedState parameter.

Remove the blank object to the right of the bar/line charts.

We need to control when the bar and line charts display, so we’ll use dynamic zone visibility for this, and for this we need another boolean field

Show Viz

{FIXED: MAX(IF [Is Selected State] THEN TRUE ELSE FALSE END)}

Is Selected State is a boolean field which essentially is 1 for True and 0 for False. If there is a state selected, the maximum value across all the data records, will be 1, so the field returns true, otherwise its 0, so false.

Use this to control visibility using value for the line and bar chart objects.

Make any further adjustments to the layout required -the size of the hex shapes may need tweaking for example. Then interact with the viz to check all is working as expected.

My published viz is here.

Happy vizzin’!

Donna

Can you use Image Role & Dynamic Zone Visibility?

Donna Coles2 Comments

New year means only one thing – a change to the WorkoutWednesday hashtag! We’re now #WOW2023, and it was Lorna’s turn to kick the next year of challenges off with a focus on the latest Tableau features. This challenge uses the image role function introduced in 2022.4 and the dynamic role visibility feature introduced in 2022.3 (so you need to make sure you’re using at least 2022.4 to complete this).

Setting up the data source

Lorna provided an excel based data sheet which I downloaded. It contains several tabs, but the two you need to relate are Player Stats and Player Positions, relating as Lorna describes on both the Game ID and the Player Name

The data needs to be filtered to just the men’s competition, so I did this by adding a data source filter (right click on the data source -> Edit Data Source Filter) where Comp = Rugby League World Cup. Doing this meant I didn’t have to worry about adding fields to the Filter shelf at all.

Building the scatter plot

For the basic chart, simply add All Run Metres to Columns and Passes to Rows, then add Game ID and Name to the Detail shelf.

We need to identify which player and game is selected when a user clicks, so we need parameters to capture these.

pSelectedGameID

integer parameter defaulted to 0

pSelectedPlayer

string parameter defaulted to nothing/empty string/ ”

Show these parameters on the canvas, and then manually enter Brandon Smith and 21.

We now need calculated fields to reference the parameter values

Is Selected Player

[Name] = [pSelectedPlayer]

Is Selected Game

[Game ID] = [pSelectedGameID]

Both return a boolean True / False value, and we can then determine which combination of player/game each mark represents, which we need in order to colour the marks.

Colour : Scatter

IF [Is Selected Game] AND [Is Selected Player] THEN ‘Both’
ELSEIF [Is Selected Player] THEN ‘Player’
ELSEIF [Is Selected Game] THEN ‘Game’
ELSE ‘Neither’
END

Add this field to the Colour shelf, change the mark type to Circle and adjust the colours accordingly. Manually sort the order of the values so that the light grey ‘Neither’ option is listed last (which means it will always be underneath any other mark).

While not stated in the requirements, the marks for the selected player are larger than the others, so add Is Selected Player to the Size shelf, and adjust the size range to suit.

Adjust the tooltip and name the sheet Scatter.

Building the Bar Chart

On a new sheet, add Player Image and Game ID to Rows and All Run Metres and Passes to Columns. Add Is Selected Player to Filter and set to True. Edit the title of the sheet so it references the pSelectedPlayer parameter, and align centre.

Set the Player Image field to use the actual image of the player stored at the URL, by clicking on the ABC datatype icon to the left of the field in the data pane, and selecting Image Role -> URL

This will change the data type icon and update the view to show the actual player image

Add Is Selected Game to the Colour shelf on the All marks card and adjust accordingly. Remove the tooltips.

We need to show the axis titles at the top, rather than bottom. To do this add another instance of the All Run Metres field to the right of the existing one. Make dual axis and synchronise axis. Repeat with the Passes field by adding another instance to the right of the existing one and making dual axis again.

Set the mark type on the All marks card back to bar.

Right click on the All Run Metres bottom axis, remove the title, and set the tick marks to none. Repeat for the Passes bottom axis.

Right click on the All Run Metres top axis, and just set the tick marks to none. Again repeat for the Passes top axis.

Adjust the font of both axis title (right click axis -> format; I just set to Tableau Book) and then manually decrease the height of the axis.

Finally click the Label button on the All marks card and check the Show mark labels check box.

Then remove all column and row divider lines.

Adding the interactivity

On a dashboard, add a Vertical Layout Container, then add the bar chart into it and then add the scatter plot underneath. Remove the container that contains all the legends and parameters – we don’t need to show any of these. Hide the title of the scatter plot.

Add a dashboard parameter action to set the pSelectedPlayer parameter on select of a circle on the scatter plot. Set this parameter to pass the Name field into the parameter, and when clearing the selection, set the value to empty string/ nothing / ”

Select Player

Add another similar dashboard parameter action, which sets the pSelectedGame parameter in a similar way. This time, the Game ID field is passed into the parameter which is then set to 0 when the selection is cleared.

Select Game

If you test clicking on the scatter plot, you should now see the bar chart data disappear (although some white space about the height of the title remains) and you’ll also see that the circle selected is ‘highlighted’ compared to all the others.

To resolve the highlighting issue, navigate back to the scatter plot sheet, create a new calculated field called Dummy which just contains the string ‘Dummy’. Add this field to the Detail shelf.

Navigate back to the dashboard, and add a dashboard highlight action, that on select on the scatter plot, targets the same sheet on the same dashboard, but only focus on the Dummy field.

Deselect Marks

To make the whole section where the player bar chart is displayed, completely collapse, we need another parameter

pShowPlayerDetail

boolean parameter defaulted to false

We also need a boolean calculated field called

Show Player Detail

TRUE

Add this to the Detail shelf of the Scatter plot sheet.

Then navigate back to the dashboard, and create another parameter dashboard action, which on select of a circle on the scatter plot, sets the pShowPlayerDetail parameter based on the value from the Show Player Detail field. When the selection is cleared, reset the parameter to False.

Show Player Detail

Finally select the bar chart on the dashboard, then click the Layout tab on the left and side, and tick the Control visibility using value check box, and in the drop down select Parameters – > pShowPlayerDetail.

Now when a circle is clicked on, the bar chart section will completely disappear and the scatter plot will fill up the whole space.

Finalise the dashboard by adding the title and legend. My published viz is here.

Happy vizzin’!

Donna

Can you do this in one dashboard?

Donna ColesLeave a comment

For this week’s #WorkoutWednesday challenge, Lorna revisited a challenge set by Ann Jackson in 2019 which I completed and blogged about here. In that challenge we were using new navigational features introduced in v2018.3. In this recreation, we’re making use of the dynamic zone visibility feature introduced in v2022.3 (so you’ll need at least that version of Tableau to progress).

I used 5 worksheets to build this viz and, as required, just 1 dashboard. 4 of the worksheets relate to the KPI blocks, and the other for the bar chart.

Building the KPI blocks

I started by creating new measures

Count Customers

CountD([Customer Name])

Count Orders

COUNTD([Order ID])

Count Cities

COUNTD([City])

Count Products

COUNTD([Product Name])

On a new sheet, add Count Customers to Text. Then add Measure Names to Filter and filter to just show the Count Customers measure. Then add Measure Values to Text and Measure Names to Text. Remove the original Count Customers from Text. Set the view to Entire View and then centre align and format the text. Set the mark type to Square and increase the Size as large as possible. Set the Colour to the relevant colour and adjust the font to match mark colour. Remove any tooltips from showing by unchecking show tooltips on the Tooltip shelf. Name the sheet Customers.

Repeat the process for creating sheets for City, Products and Orders. You may find you need to format some of the measures to have no decimal places.

Finally add Aliases to Measure Names to change the display of the measure from being Count XXXX to just XXXX (right click on Measure Names in the left hand data pane – > Aliases

Note you could possibly have named the measures just Orders, Cities etc to start with, though I think a measure called Orders already existed, so I chose this way to be consistent.

Building the bar chart

We’re going to use Dimension swapping for this chart – that is build a single chart but use a parameter to determine which dimension needs to be displayed.

pDimension

String parameter which is hardcoded initially to the word Customers.

Create a new field which will determine which dimension to show

Dimension to Display

CASE [pDimension]
WHEN ‘Orders’ THEN [Order ID]
WHEN ‘Customers’ THEN [Customer Name]
WHEN ‘Products’ THEN [Product Name]
WHEN ‘Cities’ THEN [City]
END

Note the names in the CASE statement to be stored in the parameter need to match the aliases we defined above.

On a new sheet, add Dimension to Display to Rows and Sales to Columns and sort by Sales descending. Add both Dimension to Display and Sales to Label. Format the label as required and align left. You may need to widen the rows to see the text.

Add pDimension to Colour and set the colour for the Customers dimension. Set the font to match mark colour.

Remove all axis and header columns (uncheck show header), all gridlines and row/column dividers. Add a title to the chart which references the pDimension field.

Now update the pDimension parameter to the word Orders, and set the colour. Repeat for Cities and Products.

Building the dashboard

Add the objects on to the dashaboard in such a way that you have a Vertical container that contains a Horizontal container with Customers and Orders side by side, then another Horizontal container underneath (still within the Vertical container), with City and Products side by side, and then finally add the Bar chart underneath. Set the 4 KPI block sheets to fit entire view and the bar chart to fit width. Try not to be tempted to adjust and heights of widths of objects manually on the dashboard, as that can affect things when we try to collapse later. You’ve probably got something like this:

Add a parameter action to drive the setting of the pDimension parameter

Select KPI

On select of any of the KPI block sheets, update the pDiemsion parameter passing through Measure Names. When selection is cleared, set the value to <empty string>/nothing

If you manually set the pDimension parameter to empty, your display should look like

Remove the titles from displaying from the KPI block sheets.

Hiding and showing the relevant sheets

To achieve this, we’re making use of Dynamic zone visibility functionality. For this we need some boolean fields to be created.

Crate a new calculated field

Dimension Selected

[pDimension]<>””

Similarly, create a new calculated field

Dimension Not Selected

[pDimension]=””

Back on the dashboard, select the Customers KPI block sheet (easiest way is to click on the object in the Item hierarchy pane at the bottom of the layout tab on the left hand side, as you may have trouble selecting on the dashboard itself due to the interactivity added). Once the sheet is selected it will have a grey border around it. On the layout tab on the left, select the Control visibility using value checkbox and select the Dimension Not Selected field.

Repeat this against the other 3 KPI block sheets.

Then for the bar sheet, do the same, except this time, select the Dimension Selected field instead. As soon as you do this, the bar should disappear, but reappear once you click on a KPI.

And that should be it. My published viz is here

Happy vizzin’!

Donna

Can you do YoY comparisons?

Donna ColesLeave a comment

Community month for the #WOW2022 team continued this week, with Liam Huffman setting this challenge to build a year on year comparison chart with twist.

Typically when building YoY charts you compare the month from last year with the same month this year, or a week from last year with this year (eg week 3 2021 v week 3 2022), or a specific date last year with this year (eg 4th March 2021 with 4th March 2022). However for this challenge, the focus was on being able to compare based on equivalent weekdays (at least that’s what I understood from reading). By this I mean if 10 October 2022 is a Monday, that needs to be compared with the equivalent weekday in the previous year. 10 October 2021 was a Sunday, so we actually need to compare Monday 10 Oct 2022 with Monday 11 Oct 2021.

So that’s the direction I took with this challenge, but in doing so couldn’t get the numbers to exactly match with Liam’s solution. Unfortunately I just couldn’t get my head around Liam’s approach even after looking at it. So I’m blogging this based on my interpretation of the requirement, which may be flawed.

Baselining the Dates

When building YoY charts like this which need to be flexible based on the date part selected, you need to ‘baseline’ the dates; well that’s the term I use, others might refer to it as normalising. Ultimately you need to get the dates that span multiple years to all align to the same year, so you have a single x-axis that just spans a single year.

Now based on what I talked about above, its not just a case of changing the year of every Order Date to match for all the records, we need to find the equivalent weekday from the previous year and align that.

To do this I first created a parameter

pToday

date parameter defaulted to 10 Oct 2022

This is simply to provide a constant end point. In a business scenario when the data changes, any reference to pToday would just use TODAY().

I then created

1st Day of Current Year

DATE(DATETRUNC(‘year’, [pToday]))

This returns 1st Jan 2002

I then worked out what weekday this was

Weekday of 1st Day Current Year

//determine the day of the week this year started on, based on todays date
DATENAME(‘weekday’, [1st Day of Current Year])

This returns Saturday

I then wanted to find the date of the first Saturday in every year, but firstly I needed to determine the weekday for each Order Date

Weekday

DATENAME(‘weekday’, [Order Date])

Date of 1st weekday Per Year

//get the first date in each year which falls on the same weekday as the first day of the current year
//ie 1st Jan 2022 is a Saturday. This is day 0 for 2022. We need to find day 0 for every other
//year, which is the date of the first Saturday in that year

{FIXED YEAR([Order Date]): MIN(IF [Weekday]=[Weekday of 1st Day Current Year] THEN [Order Date] END)}

For each year, get all the dates which fall on a Saturday, then returned the earliest one of those.

Popping all this information out in a table, we can see that in 2019, the first Saturday in the year was 5th Jan 2019, so all the records for dates in 2019 are stamped with 5th Jan 2019.

This Date of 1st weekday Per Year is essentially day 0 for each year. We now need to record a number against each day in the year

Days From Date

//get the number of days from the date of 1st weekday per year
DATEDIFF(‘day’, [Date of 1st weekday Per Year], [Order Date])

And with this number, we can now record the equivalent date in 2022 against each date

Baseline Date

//need to normalise all dates for every year to the current year,
//ie Day 0 for every year = 01 Jan 2022

DATE(DATEADD(‘day’, [Days from Date], [1st Day of Current Year]))

Add these fields into the tabular view and you should hopefully see how this is working

Building the Line Chart

We need some additional parameters to help build the chart.

pDatePart

string parameter defaulted to Month, with 3 values listed as shown below

pStartDate

date parameter set to use the 1st Day of Current Year when the workbook opened, meaning current value was 1st Jan 2022

pEndDate

date parameter set to use the pToday parameter when the workbook opened, meaning current value was 10th Oct 2022

With the pDatePart field, we need to define the actual date we’re going to plot

Date to Plot

DATE(DATETRUNC([pDatePart], [Baseline Date]))

when this is set to week, the date for all days in the same week are set to the first date in the week. Similarly, when this is set to month, all dates in the same month are set to 1st of the month.

The data also needs to be filtered based on the start & end dates selected, so we need

Dates to Include

[Baseline Date]>= [pStartDate] AND [Baseline Date]<=[pEndDate]

Add this to the Filter shelf of the tabular view and set to True. Also add Date to Plot into the table, and show all the parameters. Change the dates and the date part field and familiarise yourself with how the parameters impact the data.

Now we’re happy we’ve got all the key fields we need, then create a new sheet, show the parameters and add Dates to Include = True to Filter.

Then add Date To Plot as an exact date continuous (green) pill to Columns and Sales to Rows.

Create

Order Date Year

YEAR([Order Date])

and add to Colour. Adjust the colours accordingly.

Format the Sales axis, so the numbers are displayed as numbers rolled up to thousands (k) with 0 dp. Edit the Date to Plot axis to remove the title, then format the dates displayed to be in dd mmm custom format. Remove all gridlines and zero lines. Only axis lines should be displayed. Update the title.

Add Date to Plot to Tooltip as a discrete attribute (blue pill). Add MIN(Order Date) to Tooltip too. Adjust tooltip wording to suit.

Building the bar chart

Add Order Date Year to Rows and Sales to Columns. Sort Order Date Year descending.

Add Order Date Year to Colour. Adjust the opacity to about 50% and add a border. Widen the rows. Add Sales to Label and format label to be $K to 1 dp.

Add another instance of Sales to Detail, then adjust to use a Quick Table Calculation of Percent Difference. Move this pill from Detail to Label. Adjust the table calculation so it is relative to Next rather than previous

Format the Sales field that has the table calc, so it is custom formatted to ↑ 0.0%; ↓ 0.0%

Modify the label so the % change is in ( ) .

Add MIN(0) to Columns (type directly in to the columns shelf). Remove the two Sales fields from the marks card, and add another instance of Order Date Year to the Label shelf. Adjust the Label so the font is larger, matches mark colour and is rotated.

Make the chart dual axis, and synchronise axis.

Turn off all tooltips, hide the Order Date Year column and both axis. Remove all gridlines and row & column borders. Add a title. Remove any instance of Measure Names that may have been added to the Colour shelf of either marks card.

And add Dates To Include = true to the Filter shelf.

Adding the interactivity

Add the sheets onto a dashboard and adjust the layout to match. I floated the parameters and positioned with some floating text boxes too, to get the desired display.

Add a highlight dashboard action

Highlight Trend

which on hover of the bar, highlights the trend line via the Order Date Year field only.

To manage the filtering of the bars, I decided to use a parameter action, by passing the date related to the point selected. For this I created

pDateHovered

I used a string parameter, so I had a value I could use to reset to

I then needed to create an additional field

Date to Plot String

STR([Date to Plot])

and I added this to the Detail shelf on the trend sheet. This needs to be set to be an attribute, so the lines remained joined up.

Additionally I needed

Filter On Hover

[pDateHovered] = ” OR [Date to Plot] = DATE([pDateHovered])

which I added to the Filter shelf of the Bar chart sheet and set to true.

Then back to the dashboard, create a parameter action

Filter Bars

which on hover on the trend chart, updates the pDateHovered parameter passing through the Date To Plot String field, and resets back to <empty string> when released.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

Let’s Build a KPI Dashboard

Donna ColesLeave a comment

Sean set this fun and very relevant challenge this week displaying key measures (KPIs) along with further details for a selected measure displayed in a horizontal layout rather than the more traditional long-form, using ranking. The ‘gotcha’ part of this challenge was ensuring the tooltips displayed the measures in the right format, ie $ for Sales and Profit, % for Profit Ratio and a standard number to 0 dp for # of Orders.

We’ll start by

Building the KPI sheet

The data source I connected to already had Profit Ratio included, but if yours doesn’t, you’ll need to create as

Profit Ratio

SUM([Profit])/SUM([Sales])

You’ll also need to create

# of Orders

COUNTD([Order ID])

On a new sheet, double-click Sales and then double-click Profit to add them both to a sheet, and then use Show Me to display the fields as a text table.

Move Measure Names from Rows to Columns, then add Profit Ratio and # of Orders into the Measure Values section under the marks card. Re-order the measures into the required order.

Modify the format of each pill in the Measure Values section to the relevant format (millions, thousands, %)

Add Measure Names to the Text shelf, and adjust the text to be aligned middle centre and resize the fonts.

Hide the header (uncheck Show Header against the Measure Names in the Columns, and remove row dividers. Set the Tooltip not to show.

Building the Ranked Chart

The value to display in this chart is dependent on the KPI measure clicked on. We’re going to use a parameter to help drive this behaviour, so start by creating

Measure to Display

string parameter defaulted to ‘Sales’ containing 4 options: Sales, Profit, Profit Ratio, # of Orders

Based on the parameter, we need to determine which value to display

Selected Value

CASE [Measure to Display]
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Profit Ratio’ THEN [Profit Ratio]
WHEN ‘# of Orders’ THEN [# of Orders]
END

On a new sheet, add Sub-Category to Rows and Selected Value to Text and sort by Sub-Category descending. Show the Measure to Display parameter on the sheet and play around with the different options to see how the viz changes. When you’re happy it’s working as expected, reset back to ‘Sales’.

We’ll need to display the rank value, so create

Rank Value

RANK_UNIQUE([Selected Value])

and format this to be a number with 0dp but pre-fixed by #

Add this into the table.

Now, to display this information, we need to distribute each Sub-Category across 5 rows and 4 columns which is based on the Rank Value. So let’s work out which row and column each entry should be in

Rows

IF [Rank Value]%5 = 0 THEN 5 ELSE [Rank Value]%5 END

If the rank is divisible by 5, then place in the 5th row, otherwise place in the row associated to the remainder when divided by 5.

Cols

IF [Rank Value] <=5 THEN 1
ELSEIF [Rank Value] <=10 THEN 2
ELSEIF [Rank Value] <=15 THEN 3
ELSE 4
END

Add these fields to the table, so you can see how they’re working.

The Rank Value, Rows and Cols fields are all table calculations, and should be set to explicitly compute using Sub-Category.

On a new sheet, add Sub-Category to Detail, Cols to Columns as a blue discrete pill and Rows to Rows as a blue discrete pill. Add Selected Value to Columns. Drag Sub-Category from Detail on to Label and adjust to align the labels left. Widen each row slightly, so the text is visible. Adjust the Colour of the bars to be a pale grey.

Change the Measure to Display parameter to ‘Profit’, and notice what happens to the bars & labels associated to those with a negative profit. They’re positioned where you’d expect, on the negative axis, but this doesn’t match Sean’s solution.

Sean has chosen to display the values as absolute values (although the tooltips display the negative values). This means we need

Abs Value to Display

ABS([Selected Value])

Drag this field and drop directly on top of the Selected Value pill on the Columns shelf – this will replace the measure being displayed.

Now that’s sorted, we can get the ranking added.

Double click in the space next to Abs Value to Display on Columns and type in MIN(0) to create an additional axis and create a 2nd marks card.

On the MIN(0) marks card, move Sub-Category to the Detail shelf and then add Rank Value to the Label shelf. Make sure the table calc is set to compute by Sub-Category as before.

Change the mark type of the MIN(0) card to Gantt Bar, set the size to the smallest possible, and colour opacity to 0. The change of the mark type, will have changed the position of the rank label to be aligned to the left of the mark.

Make the chart dual axis, synchronise the axis and change the mark type of the Abs Value to Display back to Bar. Remove Measure Names from both of the marks cards.

Hide both axis and the headers and remove grid lines and divider lines. Make the zero line on the columns a solid line, slightly thicker.

Formatting the Tooltips

The tooltips display the selected measure name and associated value, formatted appropriately. As we’re using a ‘generic’ field to display the value, we can’t format this field as we’d usually do. Instead, I chose to resolve this using dedicated fields to store each value based on the measure selected.

Tooltip: Sales

IF [Measure to Display]=’Sales’ THEN [Sales] END

format this to $ with 0dp.

Tooltip: Profit

IF [Measure to Display]=’Profit’ THEN [Profit] END

format this to $ with 0dp.

Tooltip: Profit Ratio

IF [Measure to Display]=’Profit Ratio’ THEN [Profit Ratio] END

format this to 5 with 2 dp

Tooltip: #Orders

IF [Measure to Display]=’# of Orders’ THEN [# of Orders] END

format this to a number with 0 dp.

Add all these fields to the Tooltip shelf on the Abs Value to Display marks card, and then amend the tooltip so all these fields are listed side by side with no spacing. As only one of the fields will only ever contain a value, the correctly formatted figure will display. The Tooltip will also need to reference the Measure to Display parameter to act as the ‘label’ for the value displayed.

Finally adjust the title of the sheet to also reference the Measure to Display parameter,

Adding the interactivity

Add the sheets to a dashboard, then add a parameter action

Select Measure

On select of the KPI sheet, update the Measure to Display parameter, passing through the Measure Names value. When the measure is unselected, revert the display back to ‘Sales’.

Bonus – Colour the bars

As an added extra to call out the fact the negative values were being displayed on the positive axis, my fellow #WOW participant, Rosario Gauna, chose to colour the bars.

Colour Bar

[Selected Value] < 0

Add this to the Colour shelf of the Abs Value to Display marks card and adjust colours according.

My published viz is here. Enjoy!

Happy vizzin’!

Donna

Let’s make a dynamic relative date filter!

Donna ColesLeave a comment

Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…

The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.

I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.

So let’s crack on.

Building the initial chart

I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.

We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection

pDateLevel

string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.

Then we need a new date field to show the dates at the level specified

Display Date

DATE(DATETRUNC([pDateLevel], [Order Date]))

Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.

Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.

Setting up a scaffold data source

So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.

I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets

DateParts

A single column headed Datepart containing the values Year, Quarter, Month, Week, Day

Relative Options

3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)

I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).

Building the Date Part Selector

From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.

Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.

We’re going to need another parameter to capture the date part that will be clicked.

pDatePartSelected

string parameter defaulted to ‘quarter’ (note case)

We also need a new calculated field to store the value of the date parts in lower case

Datepart Lower

LOWER([Datepart])

and then we can use these fields to work out which date part has been selected on the viz

Selected Date Part

[pDatePartSelected] = [Datepart Lower]

Add this to the Colour shelf, and adjust colours accordingly (False is white).

Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.

Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.

In the DateParts data source, create new calculated fields

True

TRUE

False

FALSE

and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.

Building the Relative Date Radio Button Selector

In the Relative Options data source, create the following fields

Row

IF ([Option ID] %3) = 0 THEN 3
ELSE ([Option ID] %3)
END

Column

INT([Option ID]<=3)

These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.

We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed

pSelectPeriods

integer parameter

We can now create

Option Label

IF [pDatePartSelected] = ‘day’ THEN
IF [Option ID]=1 THEN ‘Yesterday’
ELSEIF [Option ID] =2 THEN ‘Today’
ELSEIF [Option ID] = 3 THEN ‘Tomorrow’
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
ELSE
IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected]
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
END

This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…

Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed

We’re going to need to identify which of the options has been selected. This will be captured in a parameter

pOptionSelected

integer parameter defaulted to 2

and then we need to know if the row of data matches that selected, so create a calculated field

Selected Relative Date Option

[Option ID] = [pOptionSelected]

Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.

As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.

Restricting the dates displayed

Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter

pAnchorDate

date parameter, defaulted to 31 Dec 2022

From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.

In the Superstore data source, create

Compare Date

DATE(DATETRUNC([pDatePartSelected],[pAnchorDate]))

Then from this, I created

Dates To Show

CASE [pOptionSelected]
WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])

WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))

WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))

WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])

WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))

WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate])
ELSE FALSE
END

This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.

If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes

Building the data range display

On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.

Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.

Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.

On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.

Putting it all together

On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.

You should have something similar to the below.

Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.

Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.

Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.

Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.

Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added

Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.

Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.

Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.

Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button

This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button

I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option

I then added a border to this option and positioned it (through trial and error) where I wanted it to be.

Adding the interactivity

The final step is to now add all the required dashboard action controls.

At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂

We need to set which date part has been selected on the control. This is a parameter action

Set Date Part

On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.

Add another parameter to capture which radio button has been selected

Select Relative Date Option

On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).

Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action

Deselect Dale Part Selecteor

on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared

Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.

And with all that, the dashboard should now be built and functioning as required.

My published solution is here.

Happy vizzin’!

Donna