Tag: Parameter Actions

Can you add candlesticks to bar charts?

Donna ColesLeave a comment

For the final week of global recognition month, Shunta Nakjima set this challenge inspired by one of the ‘founders’ of #WorkoutWednesday, Andy Kriebel.

Let’s get stuck in, by starting with the selector sheets.

Building the Measure Selector

The measure selector will be used to set a parameter which will store the particular measure selected, so we need

pSelectedMeasure

string parameter defaulted to the value Sales

We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.

Measure Selector Alias

CASE [Segment]
WHEN ‘Consumer’ THEN ‘Sales’
WHEN ‘Corporate’ THEN ‘Profit’
ELSE ‘Quantity’
END

Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.

Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.

We need to identify which measure has been selected, both through colour and an arrow indicator. So we need

Is Measure Selected

[Measure Selector Alias] = [pSelectedMeasure]

Add to the Colour shelf and adjust to suit.

Then create

Measure Selected Arrow

IF [Is Measure Selected] THEN ‘►’ END

I use this site to get the characters I need.

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.

Name the sheet Measure Selector.

Building the Year Selector

In order to not ‘hardcode’ the latest year, we need

Current Year

{FIXED:MAX(YEAR([Order Date]))}

format this to be a number with 0dp and not to show the thousands separator.

From this we can create

Comparison Year

IIF(YEAR([Order Date])<>[Current Year],YEAR([Order Date]),NULL)

On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).

As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.

We’re going to need a parameter which will capture the year selected

pSelectedYear

integer parameter defaulted to 2022 with the display format set to not include the thousand separator

We need to identify which year has been selected, both through colour and an arrow indicator. So we need

Is Selected Year

[Comparison Year] = [pSelectedYear]

Add to the Colour shelf and adjust to suit.

Then create

Year Selected Arrow

IF [Is Selected Year] THEN ‘►’ END

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.

Building the Current Year ‘card’

Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.

Building the bar chart

Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need

Measure to Display – Curr Year

IF YEAR([Order Date]) = [Current Year] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

and

Measure to Display – Comp Year

IF YEAR([Order Date]) = [pSelectedYear] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.

Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters

So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.

Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.

Difference

SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])

custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.

% Difference

IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0)
OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN
[Difference]/ABS(SUM([Measure to Display – Comp Year]))
ELSE 0
END

If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to ▲0.0%;▼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.

Diff is +ve

[Difference]>0

Add this to the Colour shelf (remove Measure Names) and adjust accordingly.

Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.

Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.

Adding the interactivity

Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.

To select the year and the measure, I needed parameter actions

Select Year

on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the Comparison Year field

and

Select Measure

on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field

Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action

Deselect Years

On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.

Create another similar filter action for the Measure Selector sheet, and that should then be it!

My published workbook is here.

Happy vizzin!

Donna

Can you create a Monthly Driver Analysis dashboard?

Donna ColesLeave a comment

Global Recognition month continued this week for #WOW2023, and I was able to enlist Norbert Borbas to set the challenge this week, which was published in both Norbert’s native Hungarian, as well as English.

Norbert provided a challenge based on a solution he had implemented at his company, and involved the creation of 2 dashboards with interactivity between them both. There’s a fair amount going on with this one, so let’s get cracking.

Building the Sales KPI

For this viz, we need to get information about the latest year sales in conjunction with the previous year. Rather than hardcode any years relating to the data, I created

Latest Year

{MAX(YEAR([Order Date]))}

which for the data set I was using, returns 2022. Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line).

With this I then created

LY Sales

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

to the sales for 2022. Format this to $ with 0 dp.

To get the sale for the previous year (ie 2021) I created

Previous Year

[Latest Year]-1

Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line), and then create

PY Sales

IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END

Format this to $ with 0 dp.

We then needed the % difference between these values

% Diff Sales From PY

(SUM([LY Sales]) – SUM([PY Sales]))/SUM([PY Sales])

Apply custom formatting set to +0.0%;-0.0%;0.0% which explicitly adds the + sign in front of positive values.

To help with the interactivity that is required in the dashboard, I also created

Sales Label

‘Sales’

With all these, we can build the KPI ‘card’.

Add Sales Label to Detail, and LY Sales, PY Sales, Previous Year and % Diff Sales From PY to the Label/Text shelf.

Format the text so it is aligned middle centre, and the arrange the text as required

Set the view to Entire View and stop the Tooltip from displaying (uncheck show tooltip). Name the sheet Sales KPI.

Building the Profit KPI

Repeat similar steps as above, apply formatting to the fields as required. You’ll need

LY Profit

IF YEAR([Order Date]) = [Latest Year] THEN [Profit] END

PY Profit

IF YEAR([Order Date]) = [Previous Year] THEN [Profit] END

% Diff Profit From PY

(SUM([LY Profit]) – SUM([PY Profit]))/SUM([PY Profit])

and

Profit Label

‘Profit’

Name the sheet Profit KPI.

Building the Line Chart

The line chart needs to change based on whether the Sales KPI or the Profit KPI sheet has been selected. We need a parameter to capture this ‘decision’.

pMeasureToShow

string parameter defaulted to Sales

To the determine which actual value to display we need

Line – Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END

format this to $ with 0dp.

I also created

Line – Measure to Display Axis (k)

IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END

ie the same field, but this was formatted to $ with 0 dp but display units of Thousands (k).

Having the two fields means that the axis can display in one format while the tooltip can show the more detailed value.

On a new sheet add Order Date to Columns and change to the discrete ‘month’ level (blue pill – May). Add Line – Measure to Display Axis (k) to Rows. Add Order Date to Colour. This will default to the Year level, and show all years from the data set, but we only want the latest 2 years. So create

Filter Years

YEAR([Order Date]) >= [Previous Year]

and add to the Filter shelf and set to True. Adjust colours to suit.

Add Order Date to the Label shelf and change to the Year level. By default the lines should be labelled. Edit the label and set the font to match mark colour. I also set the font to be Tableau Medium and bold. Adjust the order of the years in the colour legend, so 2022 is listed first which makes the line for 2022 sit ‘on top’ of the 2021 line.

Remove all gridlines/row column dividers, and set the axis lines to be bolder. Hide the Order Date label (right click > hide field labels for columns). Adjust the formatting of the Order Date axis, to display the months in an abbreviated form. Adjust the title of the y-axis to reference the pMeasureToShow parameter (right click the axis > edit).

Add Line – Measure to Display to the Tooltip shelf.

Adjust the Tooltip to display as

Finally, to help with the interactivity later, we will need

Month Order Date

DATEPART(‘month’, [Order Date])

This returns the number of the month ie 1 for January, 2 for February etc. Move this to the ‘dimensions’ section of the left hand data pane (drag above the line), and then add this to the Detail shelf. Change the field to be a discrete attribute

Name the sheet Line Chart.

Building the Symbol Chart

On a new sheet add Filter Years to the Filter shelf and set to True. Add Order Date to Columns and change to be at the discrete month level. Double click into the Rows shelf and manually type in MIN(0). Add Month Order Date to the Detail shelf.

We need to display coloured arrows depending on whether the change is up or down. For this we need

Symbol – Difference to Display is +ve

IF [pMeasureToShow] = ‘Sales’ THEN IIF([% Diff Sales From PY]>=0,TRUE,FALSE)
ELSE IIF([% Diff Profit From PY]>=0,TRUE,FALSE)
END

If the measure to display is Sales, and the difference in Sales from previous year is +ve, then return true, otherwise false, Else if the measure to display is Profit and the difference in Profit from the previous year is +ve, then return true, else false.

Change the mark type to Shape and then add this field to both the Colour shelf and the Shape shelf. Adjust colours and shapes accordingly.

Edit the axis and delete the title and set the major and minor tick marks to None. We need the axis to remain as we will need to ‘line up’ this chart with the line chart, and having a left hand axis will help.

Hide the months from showing (uncheck show header against the pill on Columns. Hide all gridlines, axis lines, zero lines & row/column dividers.

Name the sheet Symbol Chart.

Building the Main dashboard

Using horizontal and vertical layout containers, position the sheets in the required locations along with the title and the instructional text. Use background colours and inner & outer padding to give space between the objects.

For the line chart and symbol chart, these were placed in a vertical container, and the width of the ‘blank’ y-axis on the symbol chart widened to be in line with the axis on the line chart. The hierarchy of objects I used is pictured.

To make the Sales display on the line chart when the Sales KPI sheet is clicked, create a dashboard parameter action

Show Sales Line

On select of the Sales KPI sheet, set the pMeasureToShow parameter, passing in the value from the Sales Label field. When the selection is cleared, keep the parameter set to the current value.

And create a similar action to show the profit

Show Profit Line

On select of the Profit KPI sheet, set the pMeasureToShow parameter, passing in the value from the Profit Label field. When the selection is cleared, keep the parameter set to the current value.

We will need to return to this later, to add more interactivity, but for now we’ll move onto the analysis/drill down sheet.

Building the drill down table

We’re going to need a few more fields to build this type of display. Firstly, for the first bar chart column we need

Bar – LY Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [LY Sales] ELSE [LY Profit] END

Bar – PY Measure to Display

IF [pMeasureToShow] = ‘Sales’ THEN [PY Sales] ELSE [PY Profit] END

Format both these fields to $ with 0dp.

Add State/Province to Rows and Bar – LY Measure to Display to Columns. Sort the states by the measure descending. Adjust the colour of the bar to suit. Add Bar – PY Measure to Display to the Detail shelf and add a reference line per cell that displays the average of this measure.

Show mark labels and adjust the font of the labels to be size 8pt. Widen each row, and align the State labels to the left, and change the font to be bold & black. Reduce the Size of the bars. Remove gridlines, but add row dividers.

Double click into the columns shelf and manually type MIN(0) to create a ‘fake’ axis and generate a MIN(0) marks card.

Create a new field

Measure Rank LY

RANK(SUM([Bar – LY Measure to Display]))

and add this to the Label shelf of the MIN(0) marks card. Adjust the table calculation so it is explicitly set to Compute by State/Province. Remove the Bar – PY Measure to Display field from the Detail shelf. Change the mark type to shape.

To determine what type of shape and what colour to apply, we need

Measure Rank PY

RANK(SUM([Bar -PY Measure to Display]))

and then

Measure Rank Change

IF [Measure Rank LY] < [Measure Rank PY] THEN ‘Up’ ELSEIF [Measure Rank LY] > [Measure Rank PY] THEN ‘Down’
ELSEIF ISNULL([Measure Rank LY]) THEN NULL
ELSE ‘N/A’
END

Add this field to both the Shape and the Colour shelf, and adjust the table calculation so it is explicitly set to Compute by State/Province.

Adjust the shapes, and use a transparent shape against the Null option (see here for details). Adjust colours to suit. Increase the size of the shape, and align the label to the left.

For the next column, create

Bar – Measure Difference

SUM([Bar – LY Measure to Display]) – SUM([Bar -PY Measure to Display])

and custom format to +”$”#,##0;-“$”#,##0

Add to the Columns shelf, and labels should automatically get added.

Create a field

Bar – Measure Diff is +ve

[Bar – Measure Difference] >=0

and add to the Colour shelf of this marks card. Adjust colours to suit.

For the final column, we need to separately identify the values when the YoY measure difference is positive from those that are negative, and then apply ranking to each of these fields. So we need

+ve Measure Diff

IF [Bar – Measure Diff is +ve] THEN [Bar – Measure Difference] END

-ve Measure Diff

IF NOT([Bar – Measure Diff is +ve]) THEN [Bar – Measure Difference]*-1 END

Note, as the difference in this instance is negative, the values returned will also be negative, but when it comes to ranking, we want the record with the biggest negative difference to be ranked 1st ie if one value had a difference of -10 and another had a value of -100, in typical ranking, -10 is ‘higher’ than -100, so -10 would be ranked 1 and -100 2. But we want -100 to be ranked 1. So by multiplying the values by -1 in the calculation we actually return values 10 and 100. So when we rank them later, 100 is ranked 1 as it is bigger than 10.

Ranke +ve Measure Diff

RANK_UNIQUE([+ve Measure Diff])

Rank -ve Measure Diff

RANK_UNIQUE([-ve Measure Diff])

We will be displaying the information for the positive and negative ranks in separate ‘columns’ which we can do with

Rank YoY X-axis

IIF([Bar – Measure Diff is +ve], 1,2)

Add this field to columns and change the mark type to Circle. Add Bar – Measure Diff is +ve to Colour. Add Rank +ve Measure Diff and Rank -ve Measure Diff to Label. Ensure the table calculations for both fields are explicitly set to Compute by State/Province. Increase the Size of the circle, and align the label to be middle centre using a bold white font.

Now we have all the information displayed, we need to sort the tooltips.

This sheet, will be accessed through interaction and will be ‘filtered’ to just a specific month. For now, we’ll ‘hardcode’ the month by adding Month Order Date to the Filter shelf and selecting 3 (for March).

On the All marks card, add Latest Year, Previous Year, Month Order Date, Measure Rank PY to the Tooltip shelf.

We will also need

TOOLTIP – Rank statement decrease

IF [Measure Rank Change] <> ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank decreased vs. same month last year.’ END

TOOLTIP – Rank statement increase

IF [Measure Rank Change] = ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank increased vs. same month last year.’ END

TOOLTIP – Rank YoY Statement Negative

IF NOT([Bar – Measure Diff is +ve]) THEN MIN([State/Province]) + ‘ is a negative (Rank: ‘ + STR([Rank -ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END

TOOLTIP – Rank YoY Statement Positive

IF [Bar – Measure Diff is +ve] THEN MIN([State/Province]) + ‘ is a positive (Rank: ‘ + STR([Rank +ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END

Add all 4 of these fields to the Tooltip shelf of the All marks card. Ensure all the table calculation fields are set to explicitly Compute by State/Province.

Now adjust the tooltip with all the relevant fields, applying colouring as required

Hide the axis and hide the null indicator. Hide the State/Province column label heading. Finally, remove the Month Order Date field from the Filter shelf. The tooltip will look a bit funny at this point, but that will get sorted later.

Name the sheet Drill Down Table.

Building the drill down dashboard

Again using vertical and horizontal containers, arrange the sheet on a dashboard along with the title. Use text boxes arranged in a horizontal container directly above the Drill Down Table sheet to display the column headings.

As I didn’t want to hardcode any years, I created the following parameters

pLatestYear

integer parameter defaulted to 2022 and with a display format that did not include thousand separators.

and

pPreviousYear

integer parameter defaulted to 2021 and with a display format that did not include thousand separators.

and

pMonth

integer parameter defaulted to 3 and with a display format that did not include thousand separators.

When building the column headings, I referenced all these parameters instead.

To ‘set’ these parameters, I added Previous Year and Latest Year to the Detail shelf of both the Line Chart and Symbol Chart sheets.

I then added 3 dashboard parameter actions to the main dashboard which on select of the Line Chart or Symbol Chart sheet, set the relevant parameter with the value from the appropriate field.

To ensure the drill down gets ‘filtered’ to the month selected on the main dashboard, add a dashboard filter action

Drill down

On select of the Line Chart or the Symbol Chart, target the Drill Down Table sheet on the Drill Down dashboard, passing the selected field of MONTH(Order Date) only. Exclude all values when selection is cleared.

The final step is to add a Navigation Button to the drill down dashboard which displays the text ‘Go back to landing page’ and navigates back to the main dashboard.

And hopefully, with all that, you have a completed interactive navigational dashboard! My published version is here.

Happy vizzin’!

Donna

Help me choose my wine!

Donna ColesLeave a comment

As we continue global recognition month for #WOW2023, Flavio Matos introduced this challenge which displays a unit chart of wines by type.

An added twist was to provide the ability for the user to switch between English (UK) and Portuguese (Brazil) languages, and the excel data set provided a sheet with the data per language.

After completing my solution, I checked out Flavio, and found he built the chart for each language (and so data source) in 2 separate sheets, added to 2 different dashboards and used navigation button with a flag image to switch between 2 dashboards. I chose to go a different route, one that didn’t mean duplicating the viz. This means that if I had to alter the viz in future for some reason, I’m only doing it once.

Modelling the data

To build in a single sheet, I needed to have the data for two languages combined into one table. Connect to the excel workbook and add the Wine sheet to the canvas, then add a relation to the Vinho sheet and set the relationship to match on the Wine field from each sheet.

Building the Viz

We need a parameter to manage the language selection

pCountry

string parameter defaulted to ‘UK’

This parameter is then used to determine which of the fields we need to use on the viz, and these are determined through calculated fields.

Country to Display

IIF([pCountry]=’UK’, [Category],[Family])

Add this to Rows then add Abreviation to the Detail shelf. Sort the Country To Display field by the count of the Abbreviation field descending.

Change the Mark type to Square then add Abreviation to the Label shelf. Adjust the sheet to Entire View and then align the label to be middle centre.

Create a parameter to store the list of English options that can be selected

pOptions-UK

string parameter containing the list of options (taken from the requirements page), with All listed first. Default to All.

Then create a version to store the Portuguese options

pOptions-Portuguese

string parameter containing the list of options (taken from the requirements page), with Todos listed first. Default to Todos.

To flag the wines that are related to the options selected, we need

Wine has Tag

IF [pCountry] = ‘UK’ THEN
IF [pOptions-UK] = ‘All’ THEN TRUE
ELSEIF CONTAINS([Tags],[pOptions-UK]) THEN TRUE
ELSE FALSE
END
ELSE
IF [pOptions-Portuguese]=’Todos’ THEN TRUE
ELSEIF CONTAINS([Tags (Vinho)],[pOptions-Portuguese]) THEN TRUE
ELSE FALSE
END
END

This returns true or false based on what country has been selected, and in turn what country specific option has been selected. If the wine is tagged with that option, or all wines have been selected then true is returned, otherwise false.

And then using this field we can determine how to colour the squares.

Colour

IF [Wine has Tag] THEN
IF [pCountry]=’UK’ THEN [Type]
ELSE
IF [Type] = ‘Red’ THEN ‘Tinto’
ELSEIF [Type] = ‘Sparkling’ THEN ‘Espumante’
ELSEIF [Type] = ‘White’ THEN ‘Branco’
ELSE [Type]
END
END
ELSE
IIF([pCountry]=’UK’, ‘No Highlight’,’Nao Harmoniza’)
END

This is slightly more detailed as the Type field in each of the excel sheets are both stored in English, but the legend in the viz shows the types in Portuguese when that language is selected. Ideally the Type should have just come straight from the data source.

Add the 3 parameters to the view, and then add Colour to the Colour shelf. Set the pCountry parameter to UK, and choose Appetizers from the pOptions-UK parameter. Adjust the colours to suit. Manually sort the colour legend options, so the colours are listed Red > Rose > Sparkling > White > No Highlight.

Then clear the pCountry parameter (or set it to any value other than UK) and set the pOptions-Portuguese parameter to Aperitivos. Once again adjust colours as required and re-order.

The tooltips need to be language specific, and only display for the wines that match the options chosen. For these we need the following calculated fields

Tooltip – Wine

IF [Wine has Tag] THEN [Wine (Wine)] ELSE ” END

Tooltip – Food Pairing

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,[Food pairing],[Food pairing (Vinho)])
ELSE ”
END

Tooltip – Food Pairing Label

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,’Food pairing:’, ‘Harmoniza com:’)
ELSE ”
END

Tooltip – Notes

IF [Wine has Tag] THEN
IIF([pCountry]=’UK’,[Notes],[Notes (Vinho)])
ELSE ”
END

Add all four of these fields to the Tooltip shelf and adjust accordingly

Finally tidy up the formatting by removing row dividers and hiding the Country to Display column heading (hide field labels for rows).

Building the Country Selector

For this I needed to add the UK and Brazilian flags a custom shapes to my Tableau repository. I just sourced some images via my favourite search engine and added them to my repository as per the instructions here.

On a new sheet I then added Abreviation to Columns and also to Filter and just filtered to the first 2 options Ab and Ag. I changed the mark type to shape. I then created the field

Country Selector

IIF([Abreviation]=’Ab’,’UK’,’Brazil’)

and added this to the Shape shelf, and selected the two flags.

Remove the row dividers and hide the header (uncheck show header on the Abreviation pill).

Building the dashboard

Add the sheets to your dashboard and display both the options parameter and the colour legend. I used layout containers for most of the arrangement, but floated the colour selector viz. I then added the title in English and Portuguese in two separate text boxes. I also hid the title of the colour legend, and add 2 different text boxes with the colour legend title in English and Portuguese. My layout looked like

To only show the sections based on the language selected, we need the following fields

Country is UK

[pCountry]=’UK’

Country is Brazil

[pCountry]<>’UK’

On the dashboard, select the text box which contains the English title, then on the Layout tab, select the Control visibility using value checkbox, and choose the Country is UK field.

Repeat this for all the other objects – the other title, the food selector parameters and the colour legend titles, choosing the Country is UK or the Country is Brazil option as appropriate.

To switch the language, we need to add a dashboard parameter action

Select Country

On selection of the Country Selector viz, set the pCountry parameter by passing in the value from the Country Selectior field.

The final step is to stop the country flags from being ”greyed out’ ‘highlighted’ when one is selected (ie the other flag ‘greying out’).

Create a fields

True

TRUE

False

FALSE

and add these 2 fields to the Detail shelf of the Country Selector worksheet.

The back on the dashboard, add a dashboard filter action

Country Selector – Unhighlight

on selection of the Country Selector sheet on the dashboard, target the Country selector sheet directly, passing the fields True = False. Show all values if the selection is cleared.

And with that, the viz should be complete. My published version is here.

Happy vizzin’!

Donna

Can you build an interactive Viz in Tooltip?

Donna Coles1 Comment

This week’s #WOW2023 challenge was inspired by Sam Parson’s TC presentation where he demonstrated the concept of an interactive Viz in Tooltip (the workbook he presented is here).

I was aware when I set this challenge, that this was likely to be on the higher end of the difficulty scale, but WOW challenges to me have have always provided a source of inspiration and ideas to take forward into my day job. And by blogging the solutions, I provide myself with a guide to refer to when the need arises. When Sam presented the concept, I immediately wanted to understand how he’d done it, and by setting it as a challenge it provided me with the opportunity to dig into it, and get that documented ‘how to’ guide 🙂

As mentioned in the requirements, I built this using multiple sheets, so we’ll start by just building out most of those sheets.

Building the Scatter Plot

We’re only concerned with data over the last 2 years, so we need to define some measures relevant to these years

Current Year

ZN(IF YEAR([Order Date]) = YEAR({MAX([Order Date])}) THEN [Sales] END)

{MAX([Order Date]} is a Fixed Level of Detail calculation which returns the maximum date in the data set. This calculation is then comparing the Year associated to that date with the Year of each Order Date, and if they match, return the Sales. Wrapping in a ZN ensures a value of 0 in the event there are no Sales.

Prior Year

ZN(IF YEAR([Order Date]) = YEAR({Max([Order Date])}) -1 THEN [Sales] END)

YEAR({Max([Order Date])}) -1 returns the year associated to the latest date then decrements by 1 to get the value of the previous year.

We also need to calculate the difference between the sales across the 2 years and categorise based on the difference

Sales Performance

IF SUM([Current Year]) / SUM([Prior Year]) > 1.1 THEN ‘Increasing’
ELSEIF SUM([Current Year]) / SUM([Prior Year]) < 0.9 THEN ‘Decreasing’
ELSE ‘Static’ END

If the current year sales > 10% of the previous year sales then flag as ‘increasing’, else if current month sales < 90% of the previous year sales then flag as ‘decreasing’ else flag as ‘static’.

Add Prior Year to Columns and Current Year to Rows. Add Manufacturer, Sub-Category and Category to Detail. Change the mark type to Circle. Add Sales Performance to Colour, adjust colours to match and reduce opacity to around 80%. Re-order the colour legend to display Increasing at the top and Decreasing at the bottom. Name the sheet Scatter.

Building the bar chart

On a new sheet add Order Date at the discrete (blue) Month level to Columns. Add Current Year to Rows. Change the mark type to bar and add Sales Performance to Colour. Reduce the opacity of the colour to 80%.

Add Prior Year to Rows. Change the mark type on the Prior Year marks card to gantt bar. Remove the Sales Performance pill from the colour shelf on this marks card. Adjust the colour to black.

Make the chart dual axis and synchronise axis.

Hide the axis, remove all gridlines & row/column dividers. Format the months to be abbreviated to the first letter. Right click on the Order Date label at the top and hide field labels for columns. Set the sheet to Entire View. Name the sheet Bar.

Building the KPI

On a new sheet add Current Year to Text. Adjust the format (size & colour of font) and align middle centre. Set the sheet to Entire View. Name the sheet KPI.

Building the % Change Indicator

Firstly we need to capture the value of the % change in sales

Sales Performance % Change

(SUM([Current Year]) – SUM([Prior Year]))/ SUM([Prior Year])

Use custom formatting to format as ▲ 0.0%;▼ 0.0%

On a new sheet, add Sales Performance % Change and Prior Year to Text. Change the mark type to square and increase the size to as large as possible. Set to Entire View. Adjust the font size of the text to match the display and align middle centre. Add Sales Performance to Colour. Name the sheet % Change

Identifying the data to filter

The final sheet we need is one to serve the title of the Viz in Tooltip (ViT). It includes references to information related to the mark selected on the scatter plot, namely the Category, the Sub-Category and the Maufacturer. Before building this title sheet though, we need to understand how we’re going to identify the mark selected so we can filter other sheets based on it.

Typically, for most use cases, when you add a worksheet to the tooltip of another sheet, you want to ‘filter’ what’s displayed in the ViT based on the mark you’re hovering/clicking on.

So by default, when you add a worksheet via the Viz in Tooltip functionality (see Tableau KB here for more info), the markup that’s automatically added looks like below

<Sheet name=”My ViT Sheet” maxwidth=”300″ maxheight=”300″ filter=”<All Fields>”>

where the filter property is set to <All Fields> which is the instruction to pass information from the ‘parent’ sheet through to the ViT sheet.

For this challenge however, when a user first hovers on a mark on the scatter plot (the parent sheet), the information displayed in the Viz in Tooltip (ViT) sheets is for the whole unfiltered data set (ie display information related to the Current Year and Prior Year Sales across all manufacturers, sub-categories & categories). But once selected (clicked on) the information displayed in the ViT should be filtered just to that mark.

For this to work, we can’t use the filter property of the ViT markup. That property needs to be set to “” to ensure the resulting sheets aren’t filtered ‘on hover’. So we need another way to drive the filtering behaviour.

We need to use sets.

We’re going to use sets to capture the Manufacturer, Sub-Category and Category of the mark that is clicked on. So to start, right click on Manufacturer > Create > Set and select all values.

Manufacturer Set

Repeat the same steps to create a Category Set and a Sub-Category Set.

Create a dashboard and add the Scatter sheet to the dashboard. Then create dashboard set actions (Dashboard Menu > Actions > Add Action > Change Set Values)

Select Manufacturer

On select of the Scatter sheet on the dashboard, target the Manufacturer Set, assigning values to the set when the action is initiated, and adding all values to the set when the action is cleared.

Note – ‘assign value to set’ will replace any values already in the set (ie all values) with the relevant value based on the selection made, whereas ‘add values to set’ just appends the selected value to the values already in the set.

Repeat the above steps to create set actions for the Category Set and the Sub-Category Set

Navigate to the Bar sheet. Add Category Set, Sub-Category Set and Manufacturer Set to the Filter shelf, and click on each pill and Show Set to list the sets and their selected values on the left hand side (this is just so you can see what’s going on).

Initially you can see all values in all the sets are selected. Now navigate back to the dashboard and click on a single mark. Then come back to the bar sheet and check the results…

You should see a change to the bars as they are now being filtered by only the values which have been assigned to the set via the ‘on click’ action.

Building the title sheet

Now we have the sets established, we can build on these to generate the information needed for the title sheet.

To start with, we need to understand how many values have been captured in each set.

Count Categories

COUNTD([Category])

Count Sub-Categories

COUNTD([Sub-Category])

Count Manufacturers

COUNTD([Manufacturer])

Then we need to build up a title based on what’s been selected

Title

IF [Count Manufacturers] = 1 THEN MIN([Manufacturer])
ELSEIF [Count Sub-Categories] = 1 THEN ‘All ‘ + MIN([Sub-Category])
ELSEIF [Count Categories] = 1 THEN ‘All ‘ + MIN([Category])
ELSE ‘All Manufacturers’
END

and a sub title

Sub Title

IF [Count Manufacturers] = 1 AND [Count Sub-Categories] = 1 AND [Count Categories] = 1 THEN ‘(‘ + MIN([Category]) + ‘ > ‘ + MIN([Sub-Category]) + ‘)’
ELSE ‘* ‘ + STR([Count Manufacturers]) + ‘ Manufacturers across ‘ + STR([Count Sub-Categories]) + ‘ Sub-Categories’
END

On a new sheet, add Title and Sub Title to Text. Format the background of the worksheet to be orange, set to Entire View, then adjust the text and font format and align top left. Name the sheet Title.

Now navigate back to the Bar sheet, and for each of the fields in the Filter shelf (the set ones), make them apply to selected worksheets, KPI, % Change, and Title

As a result, across all 4 sheets (not the Scatter one), you should have the 3 set fields as filters with the ‘multiple worksheet’ symbol indicating a shared filter.

If you go back to the dashboard and click on a mark then check the Title sheet, the information displayed should update.

Building the Viz In Tooltip

Now we have (most of) the components we need, let’s start to put together the actual ViT.

On the Scatter sheet, click on the Tooltip button to open the Edit Tooltip dialog.

Start by deleting all the text.

Then from the toolbar, click Insert > Sheets > Title to add the Title sheet to the tooltip. You should have something like

The key to getting the tooltip to display ‘nicely’ is to consider the height and widths, and align the markup text. Sometimes this does take a bit of trial & error and can also look differently when published to Tableau Public.

Adjust the above, so the maxwidth =500 and maxheight = 100, filter = “” and the whole line of text is centred.

Then add the other 3 sheets, using carriage returns to add space between the sheets as required, and adjusting the heights and widths.

If you go back to the dashboard and hover on a mark, you should see the display below for All Manufacturers

and if you then click on a mark, the display should adjust to filter

Making the ViT interactive

Edit the Tooltip on the Scatter sheet, and add a section at the bottom that references the Category , Sub-Category and Manufacturer fields (add via the Insert menu again). Style the font as you wish

Now if you go back to the dashboard and click on a mark, you can then also click on one of the links added at the bottom. In this instance I clicked on the Chairs link and all the marks in the scatter plot related to chairs were highlighted and the ViT data all updated to show the values associated to the Chairs Sub-Category

This is happening ‘automatically’ due to the fact the Allow selection by category option on the Tooltip is checked. This is a feature (along with Include command buttons) I personally often switch off.

Now ideally, we’d be finished at this point, but we just need to add a final feature, due to the fact that some Manufacturers exist across multiple Sub-Categories. For example, below while I have clicked a mark that is related to the Global Manufacturer in Chairs, clicking Global in the links at the bottom highlight all the Global Manufacturers across all Sub-Categories, so we can’t get back to seeing the information just about the selected mark.

Adding the ‘Current Mark’ selection

We need to capture the ‘product hierarchy’ for each mark into a single field

Category | Sub Cat | Manu

[Category] + ‘|’ + [Sub-Category] + ‘|’ + [Manufacturer]

Add this to the Detail shelf of the Scatter sheet.

We will need a parameter to then capture the ‘product hierarchy’ for the selected mark

pSelectedMarkIdentifier

string parameter set to ”” ie empty string

On the dashboard, add a parameter action

Identify Current Mark

On hover of the scatter sheet on the dashboard, set the pSelectedMarkIdentifier parameter to the value stored in the Category | Sub Cat | Manu field. Keep the current value when the selection is cleared.

Finally, we need to have a link to select in the tooltip, so we need

Current Mark Identifier

IF [pSelectedMarkIdentifier] = [Category | Sub Cat | Manu] THEN ‘Current Mark’
ELSE ”
END

Add this to the Detail shelf of the Scatter sheet, and then update the Tooltip and add a reference to the Current Mark Identifier field

If you now go back to the dashboard and test by clicking on a mark associated to the Global Manufacturer, you should be able to click on Current Mark using the link in tooltip after clicking other links, and get back to what you would have seen in the tooltip when you first clicked on the mark.

It should just now be a case of sorting out the layout on the dashboard.

Congratulations on getting this far! My published viz is here.

Happy vizzin’!

Donna

Can you find the most popular sub-category sales by state?

Donna ColesLeave a comment

Lorna’s challenge this week involved the use of nested LoDs (level of detail calculations) and ViT (viz in tooltip). The requirements were very brief (just 4 bullet points), but that didn’t mean this would be simple!

Each bar in the bar chart represents the number of states where the labelled sub-category had the most sales. ie there were 13 states where Phones was the sub-category with the most sales.

Let’s just look at the data : on a sheet add State/Province and Sub-Category to Rows and Sales to Text. Sort the list descending. From the screen shot below, we can see that for Alabama, Chairs has the most sales, in Alberta it’s Fasteners and for Arizona it’s Phones.

We need to get to a point where we can display a single row for each State/Province and the most popular Sub-Category.

We’ll start by creating a field that stores the sales for the State/Province & Sub-Category combination

Sales for State & Sub Cat

{FIXED [State/Province], [Sub-Category]: SUM ([Sales])}

On the table view above, add this field to Text instead of the Sales pill. The data displayed should be the same.

What we then need to do is to identify the maximum sales value for each State/Province. We do this with another Fixed LoD which references the above Fixed LoD (ie a nested LoD expression)

Max Sales for State & Sub Cat

//This returns the value of sales for the subcategory with the largest sales per state
{FIXED [State/Province] : MAX([Sales for State & Sub Cat])}

Pop this into the table and you should see that the value for every row in this field is the same for each State/Province and matches the value of the first row in each pane

We can then use this to identify the Sub-Category where the values in the two columns match

Sub Cat with Max Sales for State

IF ([Sales for State & Sub Cat]) = ([Max Sales for State & Sub Cat]) THEN ([Sub-Category]) END

Add this onto Rows and the Sub-Category with the largest sales per State/Province is only listed once per pane.

Now remove Sub-Category from the view and you get 2 rows per State/Province – one Null, and one with the Sub-Category name we want.

Filter out the Nulls, by adding Sub Cat with Max Sales for State to the Filter shelf and excluding NULL. We’ve now got 1 row per State/Province with the appropriate Sub-Category

Let’s shift the data around – remove the fields from the Text shelf, and swap the order of the fields in the Rows so Sub Cat with Max Sales for State is listed first.

You can now see that for each Sub-Category we have the list of State/Provinces which we can just count using a new field.

Count States

COUNTD([State/Province])

Add this to Text and remove State/Province from Rows and sort descending and we have the data we need to build the bar chart.

Building the bar chart

  • Add Sub Cat with Max Sales for State to Rows
  • Add Count States to Columns and Label
  • Add Sub Cat with Max Sales for State to Filter and exclude Null
  • Sort Descending
  • Adjust colour of bars
  • Hide axis, Remove all gridlines, zero line etc
  • Adjust format of heading labels
  • Hide field labels for Rows to remove the Sub-Category label
  • Update the title
  • Adjust width of each row and size of bars as desired

Building the Map

To create the map, we need to be able to identify which Sub-Category we’re hovering on in the bar. For this we’re going to capture the value into a parameter

pSelecteSub-Cat

string parameter defaulted to ” / empty string

and the we need to identify the State/Provinces where the selected Sub-Category has the maximum sales

Selected States

[Sub Cat with Max Sales for State] = [pSelectedSub-Cat]

this returns a boolean true/false value based on whether the fields match or not.

On a new sheet, double click on State/Province to which should automatically generate a map. Make sure the location(Map menu > Edit Locations) is set to reference the Country/Region field, so both the US and Canadian provinces are all picked up

Change the mark type to be a filled map. Update the washout of the background layer to be 100% (Map menu > background layers) and then set the border property on the Colour shelf to be white.

Add Selected States to the Colour shelf and exclude the Nulls (right click Null in the colour legend and exclude). Add the pSelectedSub-Cat to the sheet and manually enter a value, eg Phones. Adjust the colours associated to the True & False values.

Adding the Viz in Tooltip

Back onto the bar chart sheet and edit the Tooltip. Include some introductory text that references the Sub-Category and the number of States (or leave with the default values if you wish). Then add the map sheet via Insert > Sheets > <select relevant sheet>

This will add default mark up of

<Sheet name=”<my map sheet>” maxwidth=”300″ maxheight=”300″ filter=”<All values>”>

Edit this text adjusting the height and width as appropriate and removing the <All values>text (we don’t want to filter the map to just show the states with the associated Sub-Category, we need all states to be visible, we’re just colouring based on the parameter).

My tooltip dialog looks like

Setting the parameter

Add the bar chart to a dashboard then add a dashboard parameter action (Dashboard menu > Actions > Add Action >Change Parameter)

Set Sub Cat

on hover of the bar chart on the dashboard, update the pSelectedSub-Cat parameter with the value from the Sub-Cat with Max Sales for State field.

Now if you hover over a bar, you should get a map with the associated States highlighted.

My published viz is here.

Happy vizzin’!

Donna

Can you build a dynamic drill down scatterplot in one view?

Donna Coles1 Comment

Retro month continues, with Kyle setting this challenge to recreate Andy Kriebel’s WOW challenge from 2017. A lot has moved on with the product since 2017 and this is a great example of how it can be simplified.

I completed the original challenge (see here) and was having a look to refresh myself… boy! it took a LOT more effort – sometimes I surprise myself that I managed it!

Now that we have parameters and parameter actions, the solution is WAY more simpler.

So let’s crack on…

As alluded to above, we’re going to need a parameter which is going to store the name of the state ‘on click’

pSelectedState

string parameter defaulted to ” (ie empty string)

We also need to display either the name of a State or a City dependent on the value of this parameter

Display Name

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

Pop these into a tabular view with Sales and Profit and show the pSelectedState parameter so we can test things out.

When the pSelectedState is empty, a row is displayed per State

but when pSelectedState contains the name of a State (or any text to be honest), a row is displayed per City (note all Cities are displayed, at this point, not just those for the State).

To restrict the list of Cities just to those that match the State in the pSelectedState parameter, we need

Records to Filter

[pSelectedState] = ” OR [pSelectedState] = [State]

Add this to the Filter shelf and set to True. Now the list should be restricted to the Cities in the State.

So lets’ start to build the basic viz.

Set the pSelectedState parameter to empty, then add Sales to Columns, Profit to Rows and Display Name to Text. Add Records to Filter To Filter and set to True. Change the mark type to Circle.

Create a new field

Profit Ratio

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

format to % with 1 dp and then add this to the Colour shelf.

Add this sheet to a dashboard, then add a dashboard parameter action

Set State

on select of a mark on the scatter plot chart, set the pSelectedState parameter with the value from the Display Name field.

If we now click on a state, the cities should be displayed instead – great! But if we now click a city, we don’t get what we want – boo! This is because the selection of a City has passed the name of the City which is stored in the Display Name field into the parameter, so the scatter is trying to display records relating to a State = City Name which doesn’t exist.

To resolve this, we need to pass a different field into the parameter action

Drill Value

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

Add this to the Detail shelf of the Scatter plot viz, then update the dashboard action to pass this field into the pSelectedState parameter instead

Reset the pSelectedState parameter to empty string, and then test again – clicking on a state and then clicking on a city should get you back to the states.

And that’s the core functionality achieved with 1 parameter, and 2 calculated fields!

We just need some additional fields to provide the relevant display for the title & sub title

Title text

IF [pSelectedState] = ” THEN ‘by State’ ELSE ‘for ‘ + [pSelectedState] END

Subtitle

IF [pSelectedState] = ” THEN ‘Click a State to drill down to City level’
ELSE ‘Click a City to drill up to State level’
END

Add these to the Detail shelf of the scatter viz, and then update the title of the sheet to reference the fields

Update the Tooltip and adjust the size of the axis fonts, and tidy up the dashboard layout, and you should be good to go!. My published viz is here.

Happy vizzin’!

Donna

Can you hide & show legends?

Donna Coles1 Comment

Sean chose to revisit the first challenge he participated in as part of retro-month at WOW HQ. Since the original challenge in 2018, there have been a significant number of developments to the product which makes it simpler to fulfil the requirements. The latest challenge we’re building against is here.

Building the KPIs

This is a simple text display showing the values of the two measures, Sales and Profit. Both fields need to be formatted to $ with 0dp.

  • Add Measure Names to Columns
  • Add Measure Names to Filter and limit to just Sales and Profit
  • Add Measure Values and Measure Names to Text
  • Format the text so it is centrally aligned and styled apprpriately
  • Uncheck ‘show header’ to hide the column label headings
  • Remove row/column dividers
  • Uncheck ‘show tooltip’ so it doesn’t display

Building the map

The map needs to display a different measure depending on what is clicked on in the KPIs. We will capture this measure in a parameter

pMeasure

string parameter defaulted to Profit

Then we need to determine the actual measure to use based on this parameter

Measure to Display

If [pMeasure] = ‘Profit’ THEN SUM([Profit]) ELSE SUM([Sales]) END

format this to $ with 0 dp

Double click on State/Province to automatically generate a map with Longitude & Latitude fields. Add Measure to Display to Colour. Adjust Tooltips.

Remove the map background via the map ->background layers menu option, and setting the washout property to 0%. Hide the ‘unknown’ indicator.

Update the title of the sheet and reference the pMeasure parameter, so the title changes depending on what measure is selected.

Show the pMeasure parameter and test typing in Sales or Profit and see how the map changes

Building the bar chart

Add Sub-Category to Rows and Measure to Display to Columns. Sort descending. Adjust the tooltip.

Edit the axis so the title references the value from the pMeasure parameter, and also update the sheet title to be similar.

Building the dimension selector control

The simplest way of creating this type of control is to use a parameter containing the values ‘State’ and ‘Sub-Category’. But you are very limited as to how the parameter UI looks.

So instead, we need to be build something bespoke.

As we don’t have a field which contains values ‘State’ and ‘Sub-Category’, we’re going to use another field that is in the data set, but isn’t relevant to the rest of the dashboard, and alias some of it’s values. In this instance I’m using Region.

Right click on the Region field in the data pane and select Aliases. Alias Central -> State and East -> Sub-Category.

On a new sheet add Region to Rows and also to Filter and filter to State & Sub-Category. Manually type in MIN(0.0) into the Columns shelf. Add Region to the Label shelf and align right. Edit the axis to be fixed from -0.05 to 1, so the marks are shifted to the left of the display.

We will need to capture the ‘dimension’ selected, and we’ll store this in a parameter

pDimension

string parameter defaulted to Central

(note – although the fields are aliased, this is just for display – the values passed around are still the underlying core values).

To know capture which dimension has been set we need

State is Selected

[Region] = [pDimension]

Change the mark type to Shape and add State is Selected to the Shape shelf, adjusting so ‘true ‘ is represented by a filled circle, and ‘false’ by open circle. Set the colour to dark grey.

Change the background colour to grey, amend the text style, hide the Region column and the axis, remove all gridlines/row dividers.

Finally, we will need to stop the field from being ‘highlighted’ on selection. So create two fields

True

TRUE

False

FALSE

and add both of these to the Detail shelf. We’ll apply the required interactivity later.

Building the dashboard

You will need to make use of containers in order to build this dashboard. I use a vertical container as a ‘base’ which consists of the rows showing the title, then BANs, a horizontal container for the main body, and a footer horizontal container.

In the central horizontal container, the map and the bar chart should be displayed side by side. We need each to disappear depending on the dimension selected. For this we need

Show Map

[pDimension] = ‘Central’

and

Show Bar

[pDimension] = ‘East’

On the dashboard, select the Map object and then from the Layout tab, select the control visibility using value checkbox and select the Show Map field.

Do the same for the Bar chart but select the Show Bar field instead.

Select the colour legend that should be displayed and make it a floating object. Position where you want, and also use the Show Map field to select the control visibility using value checkbox.

Adding the interactivity

To select the different measure on click of the KPI, we need a parameter action

Set Measure

On select of the KPI chart, set the pMeasure parameter passing in the value from the Measure Names field.

And to select the dimension to allow the charts to be swapped, another parameter action

Set Dimension

On select of the Dimension Selector sheet, set the pDimension parameter, passing in the value from the Region field

Finally, to ensure the dimension selector sheet doesn’t stay ‘highlighted’, add a filter action

Unhighlight Dimension Selector

On select of the Dimension Selector sheet on the dashboard, target the Dimension Selector sheet directly, and pass values setting True = False

Hopefully this is everything you need to get the dashboard functioning. My published viz is here.

Happy vizzin’!

Donna

Re-viz: Discovery Dashboard

Donna Coles1 Comment

For the next few Workout Wednesdays, the coaches will be revisiting old challenges – retro month! Erica kicked the month off with this challenge, to recreate Ann Jackson’s challenge from 2018 Week 38. I completed this challenge at the time (see here), but the additions and changes to the visual display Erica incorporated meant I couldn’t just republish it 🙂

So I built it from scratch using the data source from the google drive Erica referenced in the requirements (which I believe may be why my summary KPIs didn’t actually match Erica’s).

There’s a heck of a lot going on in this challenge – it certainly took some time to complete, which may mean this blog becomes quite lengthy… I will endeavour to be as succinct as I can, which may mean I don’t explicitly state every step, or show lots of screen shots.

Setting up the parameters

I used parameters and subsequently dashboard parameter actions to build my solution. Erica mentions set actions, but I chose not use any sets.

As a result there’s lots of parameters that need creating

pAggregate

This is a string parameter that contains the list of possible dimensions to define the lowest level of detail to show on the scatter plot (ie what each dot represents). Default to Sub-Category. Note how the Display As field differs from the Value field.

pColour Dimension

This is a string parameter that will contain the value of the dimension used to split the display into rows (where each row is coloured). This will get set by a parameter action from interactivity on the dashboard, so no list of options is displayed. Default to Segment.

pSplit-Colour

boolean parameter to control whether the chart should be split with a row per ‘colour’ dimension, or just have a single row. The values are aliased to Yes/No

pSplit-Year

another boolean parameter to control whether the chart should be split with a column per year or just have a single column. The values are aliased to Yes/No (essentially similar to above)

pX-Axis

string parameter that contains the value of the measure to display on the x-axis. This will be set by a dashboard parameter action, so no list is required. Default to Sales.

pY-Axis

Similar to above, a string parameter that contains the value of the measure to display on the y-axis. This will be set by a dashboard parameter action, so no list is required. Default to Profit.

pSelectedDimensionValue

string parameter that contains the dimension value associated to the mark that a user clicks on when interacting with the scatter plot, and then causes other marks to be highlighted, or a line to be drawn to connect the marks. This will be set by a dashboard parameter action, so no list is required. Default to <nothing>/empty string.

Building the basic Scatter Plot

The scatter plot will display information based on the measures defined in the pX-Axis and pY-Axis parameters. We need to translate exactly what the measures will be based on the text strings

X-Axis

CASE [pX-Axis]
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Quantity’ THEN SUM([Quantity])
END

Y-Axis

CASE [pY-Axis]
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Quantity’ THEN SUM([Quantity])
END

We also need to define which field will control the lowest level of detail based on the pAggregate dimension

Dimension Detail

CASE [pAggregate]
WHEN ‘Category’ THEN [Category]
WHEN ‘Sub-Category’ THEN [Sub-Category]
WHEN ‘Product’ THEN [Product Name]
WHEN ‘Region’ THEN [Region]
WHEN ‘State’ THEN [State]
WHEN ‘City’ THEN [City]
END

Similarly we need to know which field to split our rows by (the colour)

Dimension Row

CASE [pColour Dimension]
WHEN ‘Segment’ THEN [Segment]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Ship Mode’ THEN [Ship Mode]
END

but as we need different behaviour depending on whether the pSplit-Colour field is yes or no, we need

Row Display

IF [pSplit-Colour] THEN [Dimension Row]
ELSEIF [pColour Dimension] = ‘Category’ THEN ‘All Categories’
ELSE ‘All ‘ + [pColour Dimension] + ‘s’
END

If the parameter is true, then just show the value from the Dimension Row, otherwise display as ‘All Categories’ or ‘All Segments’ or ‘All Regions’ etc.

Similarly, as the columns can be split by years or not, we need

Years

IF [pSplit-Year] THEN STR(YEAR([Order Date])) ELSE ” END

Add the fields to a sheet with

  • Years & X-Axis on Columns
  • Row Display & Y-Axis on Rows
  • Dimension Detail on Detail
  • Dimension Row on Colour
  • Set the mark type to circle and reduce colour opacity
  • Edit the axes, so the titles are sourced from the pX-Axis and pY-Axis parameters
  • Show all the parameters and manually edit the values/change the selections to test the functionality.

Highlighting corresponding marks

Show the pSelectedDimension parameter and hover over a mark in the scatter plot to read the value of the Dimension Detail field. Enter than value into the pSelectedDimension parameter (eg based on what is displayed above, each mark is a Sub-Category, so I’ll set the field to ‘Phones’).

We need to determine whether the value in the parameter matches the dimension in the detail

Highlight Mark

[pSelectedDimensionValue] = [Dimension Detail]

This returns True or False. Add this field to the Detail shelf, then add it as second field on the Colour shelf.

Adjust the default sorting of the Highlight Mark field, so the True is listed before False (sorted descending) – right click on the field > Default Properties > Sort. And ensure the colour fields on the shelf are listed so Dimension Row is above Highlight Mark. If all this is done, then the colour legend show look similar to below, where the Dimension Row is listed before the True/False, and the Trues are listed above the Falses, so the True is a darker shade of the colour.

Add Highlight Mark to the Size shelf and then edit the size legend to be reversed and adjust the sizes so the smaller ones aren’t too small, but you can differentiate (you may need to adjust the overall size slider on the size shelf too).

Making a connected dot plot

Add Order Date at the Year level (blue discrete pill) to the Detail shelf of the scatter plot.

To make the lines join up when the viz isn’t split by year, we need a field

Y-Axis Line

IF NOT [pSplit-Year] AND [pSelectedDimensionValue] = MIN([Dimension Detail]) THEN [Y-Axis] END

This will only return a value to plot on the Y-Axis if pSplit-Year = No and a user has clicked on a mark.

Set the pSplit-Year parameter to No, then add Y-Axis Line to Rows. On the Y-Axis Line marks card, remove Highlight Mark from colour and size and also remove Dimension Row from Colour. Add Order Date at the Year level (blue discrete pill) to Detail. Change the mark type to Line then add Year(Order Date) to Path instead of Detail.

Make the chart dual axis and synchronise the axis.

Play around changing the pSplit-Year parameter and the value in the pSelectedDimension parameter to test the functionality.

Tidy the scatter plot by adjusting font sizes, removing the right hand axis & the gridlines, lightening the row & column dividers, removing row & column label headings. Tidy up tooltips. Add a title that references the parameter values.

Building the Total Marks KPI

Create a new field

Count Marks

SIZE()

and a field

Index

INDEX()

Set this field to be a discrete dimension (right click > convert to discrete)

On a new sheet, add Dimension Row, Dimension Detail and Order Date (set to Year level as blue discrete pill) to the Detail shelf. Add Count Marks to Text. Adjust the table calculation setting of Count Marks so that all the fields are selected.

Add Index to the Filter shelf and select 1. Then adjust the table calculation setting of this field so it is also computing by all fields. Re-edit the filter, and adjust so only 1 is selected. This should leave you with 1 mark. Change the mark type to shape and set to use a transparent shape.

Adjust font size & style, set background colour of worksheet to grey, adjust title, hide tooltips.

Building the X-Axis KPI

For this we need

Total X-Axis

TOTAL([X-Axis ])

Min X-Axis

WINDOW_MIN([X-Axis ])

Max X-Axis

WINDOW_MAX([X-Axis ])

On a new sheet add Dimension Row, Dimension Detail, YEAR(Order Date) to Detail. Add pX-Axis, Total X-Axis, Min X-Axis & Max X-Axis to Text. Adjust all the table calcs of the Total, Min, Max fields to compute using all dimensions listed. Add Index to filter and again set to 1, then adjust the table calc and re-edit so it is just filtered to be 1. Set the mark type to shape and use a transparent shape. Adjust the layout & font of the text on the label. Set background colour of worksheet to grey, adjust title, hide tooltips.

Building the Y-Axis KPI

Repeat the steps above, creating equivalent Total, Min & Max fields referencing the Y-Axis.

Creating the Y-Axis ‘buttons’

We’ll start with creating a Profit button

Create a field

Label: Profit

“Profit”

and

Y-Axis is Profit

[pY-Axis] = ‘Profit’

We will also need the field below for later on

Y-Axis not Profit

[pY-Axis] <> ‘Profit’

On a new sheet double click on Columns and manually type in MIN(1). Add Label: Profit to Text and Y-Axis is Profit to Colour. Change the mark type to bar.

Set the Size of the bar to maximum, adjust the axis to be fixed from 0-1 and hide the axis. Remove all column/row banding, axis line, gridlines etc.

Show the pY-Axis parameter. If the colour legend is set to True (as pY-Axis contains Profit), then adjust the colour to a dark grey. Then change the value of the pY-Axis parameter, which should then display False in the colour legend. Adjust this to light grey. You may need to do this the other way round. Hide tooltips.

Repeat the same process to create separate sheets for Sales and Quantity with equivalent calculated fields (I found the easiest way was to duplicate the sheet and then swap out the fields).

Creating the X-Axis ‘buttons’

Again, just duplicate the above steps but reference the pX-Axis parameter instead.

You should end up with 6 sheets (1 per measure – Sales, Profit, Quantity – per axis), and 18 calculated fields (3 per measure & axis) as a result.

Creating the ‘Select Colour’ buttons

For the Category button, create

Label: Category

‘Category’

and

Colour is Category

[pColour Dimension] = ‘Category’

Build a ‘button’ as a bar chart, using the same principals as above. You will need to show the pColour Dimension parameter to test changing the value to set the different colours.

Repeat the same steps to build 3 further sheets for Region, Segment and Ship Mode.

Building the dashboard

You will need to use layout containers to arrange all the objects in. The main body of the dashboard consists of a horizontal layout container, which contains 3 objects: a vertical container (left column with configuration options), the scatter plot in the middle and then another vertical container (right column with KPIs).

The left hand ‘Configure Your Chart’ vertical container consists of text objects, parameters, the X-Axis ‘button’ sheets, the Y-Axis ‘button’ sheets and the Colour ‘button’ sheets.

For each of the X-Axis and Y-Axis button sheets, a parameter action needs to be created like below

Set Y-Axis to Profit

On select of the Y-Axis Profit sheet (or whatever you have named the sheet), set the pY-Axis parameter with the value from the Label:Profit field.

You should end up with 6 different parameter actions for these fields – 1 per measure per axis .

For each of the ‘Colour’ buttons, a similar parameter action is also required

Set Colour to Category

On select of the Colour-Category sheet (or whatever you have named the sheet), set the pColour Dimension parameter with the value from the Label:Category field.

You should end up with 4 parameter actions like this.

The Y-Axis and X-Axis buttons should only display 2 options at a time, so you can’t select the same measure for both axis.

Assuming Profit is currently selected on the Y-Axis, then select the object that displays the Profit X-Axis ‘button’ and from the Layout tab, set to control visibility using value and select the Y-Axis not Profit calculated field. As the Y-Axis is set to Profit, the Profit X-Axis button will disappear.

Repeat these steps for each of the X & Y Axis button sheets. Note, sometimes it’s easier to select the object via the Item Hierarchy section of the Layout tab.

For the scatter plot, the user needs the ability to select a mark and the others be highlighted/connected depending on the current settings. This needs another parameter action

Select Dimension Value

On select of the scatter plot, set the pSelectedDimensionValue parameter with the value from the Dimension Detail field.

For the right hand KPI nav, the vertical container consists of text objects, and the KPI sheets.

To make horizontal line separators, set the padding on a blank object to 0, the background colour to the colour you want, and then edit the height of the blank object to 2pt.

For the Info ‘button’, add a floating text box containing the relevant text and position it where you want it to display. Give the object a border and set the background to white. Then select the object and choose the Add Show/Hide Button from the context menu. Edit the button to display an info icon when hidden. Ensure the button is also floating and position where you want.

I used additional floating text boxes to display some of the other text information on the dashboard.

No doubt you’ll need to spend some time adjusting padding and layout to get everything where you want, but this should get you the core functionality.

My published viz is here. Phew! that was epic!

Happy vizzin’!

Donna

Can you calculate a percent difference between a range of dates?

Donna Coles1 Comment

This week’s challenge, set by Lorna, made use of the newly released ‘dotted line’ format in v2023.2, so you’ll need that version in order to achieve the dotted line that spans the 2 marks being compared. If you don’t have this version, you can still recreate the challenge, but you’ll just have a continuous line.

Building the calculations

This challenge involves table calculations, so my preferred startig point is to build out all the fields I need in a tabular format.

So start by adding Category to Columns and Order Date at the discrete (blue) week level to Rows. Add Sales to Text (I formatted Sales to $ with 0 dp, just for completeness).

Apply a Moving Average Quick Table Calculation to the Sales pill, then edit the table calculation to it is averaging over the previous 5 values (including the current value – ie 6 weeks in total), and it is computing by Order Date only.

Add another instance of Sales back into the table, so you can check the values.

The ‘moving average’ Sales pill is what will be used to plot the main line chart.

But we need to identify 2 points on the chart to compare – the values associated to a start date determined by the user clicking on the chart, and the values associated to an end date determined by the user hovering on the chart. We’ll make use of parameters to store these dates

pDateClick

date parameter defaulted to 27th Dec 2020

pDateHover

date parameter defaulted to 28 Nov 2011

We can then determine what the moving average Sales values were at these two dates

Sales to Compare

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] OR DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN
WINDOW_AVG(SUM([Sales]), -5, 0)
END

Add this to the table, and the values for the moving average will only be populated for the two dates stored in the parameters

This is the field we will use to plot the points to draw the lines with.

But we also need to work out the difference between these values so we can display the labels.

Sales to Compare Start

WINDOW_MAX(IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END)

If the date matches the pDateClick (start) date then return the moving average and then spread that value over every row.

Sales to Compare End

WINDOW_MAX(IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END)

If the date matches the pDateHover (end) date then return the moving average and then spread that value over every row.

Add these into the table, and you can see how the table calculations are working

The moving avg value for the start date is displayed in every row against the Sales to Compare Start column, while the moving avg for the end date is displayed in every row for the Sales to Compare End column.

With these values now displayed on the same row, we can calculate

Difference

[Sales to Compare End]-[Sales to Compare Start]

formatted to $ with 0 dp

and

% Difference

[Difference]/[Sales to Compare Start]

formatted to a custom number format of ▲0.0%;▼0.0%;0.0%

Popping these into the tables, the values populate over every row, but when it comes to the label, we only want to show data against the comparison date, so I created

Label Difference

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [Difference] END

formatted to $ with 0 dp, and

Label Difference %

IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [% Difference] END

formatted to a custom number format of ▲0.0%;▼0.0%;0.0%

With all these fields, we can now build the chart

Building the viz

On a new sheet, add Order Date set to the continuous (green) week level to Columns and Category to Rows. Add Sales to Rows and apply the moving average quick table calculation discussed above, adjusting so it is computing over 5 previous weeks and by Order Date only

Add Category to Colour and adjust accordingly, then reduce the opacity to around 40%

Add pDateClick to Detail then add a reference line to the Order Date axis the references this field. Adjust the Label and Tooltip fields, and set the line format to be a thick grey line.

Format the reference line text so it is aligned top right.

Then add pDateHover to Detail and add another reference line, but this time set the line so it is formatted as a dashed line.

Next, add Sales to Compare to Rows. Adjust the colour of the associated marks cards so it is 100% opacity.

Right click on the Sales to Compare pill and Format. On the Pane tab on the left hand side, set the Marks property of the Special Values section to Hide (Connect Lines). Click on the Path shelf and choose the dashed display option.

Set the chart to be Dual axis and synchronise the axis. Remove Measure Names from the All marks card. Hide the right hand axis.

Add Label Difference and Label Difference % to the Label shelf. Set the font to match mark colour and adjust font size and positioning. I left aligned the font but added a couple of spaces so it wasn’t as close to the line. I also added a couple of extra carriage returns so the text shifted up too.

Finally adjust tooltips, remove column dividers and hide the Category column title. Adjust the axis titles.

Adding the interactivity

Add the sheet onto a dashboard, then add 2 parameter actions

Set Start on Click

On select of the Viz sheet, pass the Order Date (week) into the pDateClick parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).

and

Set Comparison on Hover

On select of the Viz sheet, pass the Order Date (week) into the pDateHover parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).

And that should be it.

My published viz is here.

Happy vizzin’!

Donna

Can you build a mobile KPI app?

Donna Coles1 Comment

For week 24 of #WOW2023, Kyle set this challenge involving dynamic zone visibility to build a mobile friendly KPI visual.

The charts being displayed are relatively simple, and use techniques applied several times in other challenges. Let’s tackle the charts for each ‘page’ one by one.

The Home page

For the Sales KPI

  • Double click into Columns and type MIN(1)
  • Add Sales to Label.
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog to include the word Sales and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Format the Sales measure to be $ with 0 dp.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

Duplicate this sheet and create equivalent ones for Profit and Orders – you’ll need to create a field

Count Orders

COUNTD([Order ID])

for the orders KPI.

For the Sales Sparkline

  • Add Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Duplicate the sheet and create equivalent ones for Profit and Orders.

You should have 6 sheets for the home page.

The Category Page

The Category page is displaying values for the last 12 months based on ‘today’. If building this in a business environment, I would make use of the TODAY() function. But to ensure the viz doesn’t break in future, I’ll hardcode today within a parameter

pToday

date parameter defaulted to 14 June 2023

I then need a field to restrict the records to report over

Last 12 months only

[Order Date]>= DATEADD(‘month’, -12, DATETRUNC(‘month’,[pToday])) AND
[Order Date]<DATEADD(‘month’, 1,DATETRUNC(‘month’,[pToday]))

this will return true if the Order Date associated to the record is greater than or equal to the 1st of the month, 12 months ago, based on the pToday parameter, and the Order Date is less that the 1st of next month, based on pToday.

For the Category KPI sheet

  • Add Last 12 months only to the filter shelf and set to true
  • Add Category to Rows
  • Double click on Columns and type in MIN(1)
  • Add Sales and Category to Label
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Hide the Category column
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

For the Category trend

  • Add 12 months only to filter and set to true.
  • Add Category and Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Add Sales to Label and show the min & max labels for each Category pane
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

The Segment Page

Repeat the same steps as described above for the Category Page, but replace any reference to Category with Segment.

Sales by State Page

  • Add State/Province to Rows
  • Add Sales to Columns
  • Sort by Sales descending
  • Adjust the Colour
  • Click on the Label shelf and Show mark labels
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Building the navigator

I went down a slightly longwinded route for this, but its still an acceptable method. I knew deep down it could be done in 1 sheet, but my brain just wasn’t quite wired up properly when I built it.

I basically ended up building 2 sheets per symbol.

Firstly, you’ll need to add the symbol images into your shapes palette.

Create a new field

Selection – Home

“Home”

Also create fields

True

TRUE

and

False

FALSE

  • Add Selection – Home to the Text shelf.
  • Change the mark type to Shape and select the ‘home’ shape from your custom shape palette.
  • Set to Entire View, then adjust the Label alignment to be bottom centre.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Format the background of the worksheet to medium grey
  • Add True and False to the Detail shelf.
  • Name the sheet Home – Unselected or similar

Duplicate sheet and change the background colour to teal or similar. Name this sheet Home – Selected or similar.

Repeat the process building 2 sheets for each image – you’ll need to create a Selection– Category field, a Selection – Segment field and a Selection – State field.

Building the calcs for Dynamic Zone Visibility

In order to hide and show various content ‘on click’ we will be making use of dynamic zone visibility. For this we need several boolean fields created along with a parameter

pSelection

string parameter, defaulted to Home

We then need

Is Home Selected

[pSelection] =’Home’

Is Home Not Selected

[pSelection] <>’Home’

Is Category Selected

[pSelection] =’Category’

Is Category Not Selected

[pSelection] <>’Category’

Is Segement Selected

[pSelection] =’Segment’

Is Segement Not Selected

[pSelection] <>’Segment’

Is State Selected

[pSelection] =’State’

Is State Not Selected

[pSelection] <>’State’

Building the Dashboard

We need to make use of multiple (nested) containers in order to get all the content positioned in the right place. I’m not going to go through step by step which containers to place where, but just summarise the key points.

For the ‘navigator’ strip, all 8 sheets need to be placed side by side in a horizontal container, and should be ordered so the ‘home’ sheets are first, then the ‘category’ ones etc. I adjusted the padding around each object to be 1px, and obviously didn’t show the title.

For each sheet, determine whether it should display or not by using the control visibility using value option on the layout tab, and selecting the appropriate field based on which ‘page’ the sheet relates to , and whether it’s the ‘active’ / selected sheet or not.

Eg for the teal Home – Selected sheet, the control visibility using value option should be driven based on the value of the Is Home Selected field, while the grey Home – Unselected sheet should be based on the value of the Is Home Not Selected field.

If all these are set correctly, only 4 of the 8 sheets should be visible at any one time – 1 teal and 3 grey.

For the ‘pages’ ie the set of sheets visible based on the selection in the navigator, a Horizontal Container should be used which in turn consists of 1 vertical container (for the sheets relating to the Home page), 2 horizontal containers (1 containing the 2 sheets side by side for the Category page, and 1 containing the 2 sheets side by side for the Segment page), and finally the Sales by State sheet should be added to the main horizontal container.

The Sales by State sheet should be visible based on the Is State Selected field. Each of the other containers should be visible based on their relevant field.

When putting all this together, the dashboard might look crowded and disorganised, but once the settings have been applied, only 1 page’ should be visible and then you can tweak padding and positioning if need be.

Capturing the selection

We need parameter actions to determine which card should display

Select Home

This parameter action should be applied when the Home – Selected or Home- Unselected sheets are clicked on, and it should set the pSelection parameter, passing in the Selection – Home field.

Equivalent parameter actions should then be created for each of the other Selected/Unselected sheets, passing in the appropriate Selection – xxx field.

Finally to ensure the navigation options don’t remain ‘selected’ on click (the images look darker) we need to apply filter actions to set the true field to false on each of the navigation buttons – this means 8 filter actions, which should look similar to this…

The source sheet selected on the dashboard should target the actual sheet itself (not the one on the dashboard).

Add a title and any other content onto the dashboard. Finally to ensure the viz works properly on a mobile, delete the phone layout option that is automatically listed on the dashboard tab.

My published instance is here. Check out Kyle’s solution to see the 1-sheet navigator.

Happy vizzin’!

Donna