Tag: Swap Measures

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

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

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

Can you switch between KPIs?

Donna ColesLeave a comment

In this instalment of #WOW2022, Kyle posed a challenge based on a previous viz he had published which in turn was inspired by Lindsey Poulter‘s Set Actions workbook.

I made an attempt to build a solution without referencing Lindsey’s viz, but ended up building multiple sheets and creating multiple calculations, which just ‘didn’t feel right’ – it would have worked, but it just seemed ‘too clunky’ of a solution, so I ended up checking out Lindsey’s viz as a guide. The key to the ‘simpler’ solution involves utilisation of a field in the data that allows the ‘measures’ to be arranged horizontally in a single sheet. I’ll explain further as we get to that section. I also don’t make any use of set actions in my solution. Instead I use parameter actions. So let’s crack on…

To build this solution I need 7 sheets

  • 1 sheet to display the 5 measure boxes with the summary stats per player for each measure
  • 1 sheet per sparkline that is displayed in each box (5 sheets in total)
  • 1 sheet to display the larger line chart at the bottom which changes the measure on selection of a card

Creating the measure calculations

Firstly, we need to set up the measures that are being used throughout this viz. Some are new calculations, some are just renamed fields. I renamed the following fields :

  • G -> GAMES (formatted to 0 dp)
  • H – > HITS (formatted to 0 dp)
  • HR -> HOME RUNS (formatted to 0 dp)

then I created

HITS/GAME

ROUND(SUM([HITS])/SUM([GAMES]),2)

AB/HR

ROUND(SUM([AB])/SUM([HOME RUNS]),2)

Building the measure swap line chart

I’m going to start by building out the line chart at the bottom of the dashboard, as once built and formatted, we can duplicate it to form the basis of the other 5 line charts.

As with any measure swap chart, we need to create a parameter that is going to store the value of the measure that has ben selected to show. In this instance we just need

pMeasure

a string parameter which is defaulted to the value ‘GAMES’

Then we need a field that is going to determine which measure to display based on the parameter value

Value to Display

CASE [pMeasure]
WHEN ‘GAMES’ THEN SUM([GAMES])
WHEN ‘HITS’ THEN SUM([HITS])
WHEN ‘HITS/GAME’ THEN [HITS/GAME]
WHEN ‘HOME RUNS’ THEN SUM([HOME RUNS])
WHEN ‘AB/HR’ THEN [AB/HR]
END

Show the pMeasure parameter, then add Year (as green, continuous pill) to Columns and Value to Display to Rows and Player to Colour. Adjust colours accordingly. Add Year (as blue discrete pill) to the Filter shelf, and exclude 2022, as Kyle chooses not to show this data – probably as its an incomplete year).

Manually change the values in the parameter to HITS, or HITS/GAME etc and check the display changes as expected.

On the Label shelf, select to Show Mark Labels and only label Max/Min values. Modify the colour of the label to Match Mark Colour.

Finally, format the Value to Display field to Number(Standard). This is a format that will automatically display the values as whole numbers or decimals, which is really neat (Note – it’s only while rebuilding the solution to blog that I remembered this and tried it out, so you may see that my published solution has a bit of a convoluted calculation to get the display as I needed).

Edit the Year axis to start at 2000 and end in 2022, and remove the title. Edit the Value to Display axis to not include zero and remove the title. Remove all gridlines.

Finally amend the tooltip, and then edit the title of the sheet to reference the pMeasure parameter.

Building the individual sparkline charts

We need to create a sheet per measure to be used in the sparkline display in the card. To create the first one, duplicate the Selected Measure Trend sheet you just created, then

  • Uncheck Show mark labels
  • Uncheck Show Tooltips
  • Hide the Year axis
  • Replace the Value to Display pill with the GAMES field (drag the GAMES field from the Dimensions pane and drop it directly on the Value to Display pill, so it is just replaced).
  • Hide the Games axis
  • Format to remove all zero lines/axis rulers
  • Set the worksheet background colour to None (which will make it transparent when we add it to the dashboard later, although you won’t notice anything at this point).
  • Name the sheet Games Trend or similar.

Now duplicate this sheet, and replace the GAMES pill with the HITS pill. Name this new sheet Hits Trend or similar.

Repeat this process for the HITS/GAMES, HOME RUNS, and AB/HR measures, so you have 5 trend charts.

Building the Measure Selector card

We’re going to use some Tableau trickery to ‘fake’ these cards. We’re looking to build a table of 1 row and 5 columns where each column contains 3 lines of text.

We can’t just use Measure Names & Measure Values split by Player, as we need to create a ‘box’ for each measure with the Players text displayed ‘together’ top left.

So we use some trickery.

Firstly, we’re going to ‘map’ the name of each measure to a Year in the data set which exists for each Player (ie we use the last years and not the first ones).

Metric Name

CASE [Year]
WHEN 2017 THEN ‘GAMES’
WHEN 2018 THEN ‘HITS’
WHEN 2019 THEN ‘HITS/GAME’
WHEN 2020 THEN ‘HOME RUNS’
WHEN 2021 THEN ‘AB/HR’
END

Then we need to capture the get the set of measures for each player into a dedicated field :

C Measures

IF [Player] = ‘Cabrera’ THEN
CASE [Metric Name]
WHEN ‘GAMES’ THEN {FIXED [Player]: SUM([GAMES])}
WHEN ‘HITS’ THEN {FIXED [Player]: SUM([HITS])}
WHEN ‘HITS/GAME’ THEN {FIXED [Player]: [HITS/GAME]}
WHEN ‘HOME RUNS’ THEN {FIXED [Player]: SUM([HOME RUNS])}
WHEN ‘AB/HR’ THEN {FIXED [Player]: [AB/HR]}
END
END

P Measures

IF [Player] = ‘Pujols’ THEN
CASE [Metric Name]
WHEN ‘GAMES’ THEN {FIXED [Player]: SUM([GAMES])}
WHEN ‘HITS’ THEN {FIXED [Player]: SUM([HITS])}
WHEN ‘HITS/GAME’ THEN {FIXED [Player]: [HITS/GAME]}
WHEN ‘HOME RUNS’ THEN {FIXED [Player]: SUM([HOME RUNS])}
WHEN ‘AB/HR’ THEN {FIXED [Player]: [AB/HR]}
END
END

We’re using FIXED LoDs to get the totals of each measure across all years for each Player

You can see below that the numbers highlighted match the numbers per player from the image above

With these calculations, we can now build the ‘card’.

Add Year (blue discrete pill) to Columns, and filter to only show the years 2017-2021. Type in MIN(1) on the Rows shelf. Change mark type to Bar and edit the axis to be fixed from 0-1. Set the Fit to Fit Width.

Add Metric Name, P Measures and C Measures to the Label shelf. Adjust the label so it is formatted with the right colours, and aligned left. I used some extra spaces at the start of each line so that there was a bit of padding.

To colour the bars, we need a further calculated field

Measure is Selected

[pMeasure]=[Metric Name]

Add this to the Colour shelf and adjust accordingly and reduce the opacity to 50%.

Hide all headers/axes, remove all gridlines/axis rulers and format the background colour of the worksheet to None as you did before. Don’t show the tooltip.

Finally we need to create & add a couple more fields to prevent the selected box from remaining highlighted when we click on it. Create fields

True

TRUE

False

FALSE

and add both to the Detail shelf.

Building the dashboard

Now we have the components to build the dashboard. The Measure Selector sheet and the 5 sparkline charts will all be floating objects carefully arranged. It’s up to you whether the remaining objects will also be floating or captured in tiled containers.

The key with the Measure Selector and Sparkline sheet is that all the sparkline sheets should be sent to back, so they are behind the Measure Selector card. This is where the transparency settings help as you can then ‘see through’ the sheet that is on the top.

You should also make sure each of your sparkline sheets are the same height & width and start at the same y position

Adding the interactivity

Once all your objects are placed, add a parameter dashboard action that on select of the Measure Selector sheet will set the pMeasure parameter, passing in the Metric Name field.

Then create a filter dashboard action that will on select of the Measure Selector object on the dashboard, will filter the Measure Selector sheet itself, setting the fields True = False. As this can never happen, the filter doesn’t apply, and the sheet removes the auto highlight that Tableau applies.

Hopefully, you now have a fully functional dashboard with a chart that changes on click of a KPI card in the top section. My published viz is here.

Happy vizzin’!

Donna

Let’s switch those measures!

Donna ColesLeave a comment

It was time for Lorna to add her iteration to the dashboard challenge that’s been set over the last few weeks. The challenge this week was to incorporate a measure swap control which allows the user to select which measures they want to compare.

Lorna gave the option to build out either your own or an already published solution, or to build a simpler brand new viz that just demonstrates the technique.

By incorporating this measure swap control into an existing solution, existing calculated fields and other objects will need to be adjusted/renamed. I started to build against my own solution, but that already had multiple instances of calculated fields where I’d chosen to change from a table calculation version in week 5 to an LoD based solution in week 6. I decided instead to use Kyle’s solution from week 7, which is published here.

However, for the purposes of this blog, I will build out the simple solution, so the focus is on what you need to do to build the control.

Building the Measure Selector

So to start, I simply copied the text for the secondary data source off the challenge page

I then opened Tableau Desktop, connected to the Orders sheet of the relevant Superstore Sales excel spreadsheet, then on the Data menu, selected Paste, which added a ‘clipboard’ data source and presented the data on a sheet.

To start with I chose to Alias the Number field (right click > Aliases) to map the numbers in the data source to the names of the measures we want, as below

The values selected are going to be captured within a parameter, so I then set these up

pMeasureX

Integer parameter defaulted to 1 (ie Sales)

I created a similar pMeasureY too, this one defaulted to 3 (ie Profit Margin).

For the selector control, we need to indicate which measure has been selected, so we need

X Selected?

[pMeasureX]=[Number]

and

Y Selected?

[pMeasureY]=[Number]

I then added Number to Rows and created 3 MIN(1) fields on the Columns shelf

On the first MIN(1) marks card, I changed the mark type to Shape, then added X Selected? to the Shape shelf. I adjusted the shape using options from the Ratings shape palette, and adjusted the size to suit.

On the 2nd MIN(1) marks card, I changed the mark type to Circle, reduced the size to as small as possible, and changed the colour to be completely transparent (Opacity = 0%). I then added Number to the Label shelf, changed the alignment to be Middle Centre, and adjusted the height of the rows, so all the text labels are visible.

On the 3rd MIN(1) marks card, I repeated the steps taken for the 1st marks card, but added Y Selected? to the Shape shelf instead.

I then hid the axes and the Number pill (uncheck Show Header), and removed all row/column borders and gridlines/zero lines.

When this sheet is added to the dashboard, we’re going to need to add parameter actions to pass information from this sheet into the pMeasureX and pMeasureY parameters. For this I found I needed to create copies of the Number field, as using the same field to control both selectors caused me some issues. So I created

Number (X)

[Number]

and added this to the Detail shelf of the 1st MIN(1) marks card.

And then

Number (Y)

[Number]

which was added to the Detail shelf of the 3rd MIN(1) marks card.

Another feature of this sheet when added to the dashboard, is we don’t want the circle selected to be highlighted/remain selected, so we’ll use a filter action to solve that. But again we need some additional fields on the sheet to help us with that. Create calculated fields

True

TRUE

False

FALSE

And add both of these to the Detail shelf of the All Marks card.

Finally adjust the title of the sheet to contain the text ‘X Select a Measure Y’. You’ll need to play around with the number of spaces to use. It’s best to wait until the sheet is on the dashboard to get it right.

Building the Scatter Plot

On a new sheet, the first thing needed for this challenge is to create

Profit Ratio

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

Now we need to define fields that will determine what measure to display based on the value stored in the relevant parameter

X Measure

CASE [pMeasureX]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Y Measure

CASE [pMeasureY]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Add X Measure to Columns and Y Measure to Rows, and add State to Detail.

We need to label the X & Y axis based on the name of the selected measure, so we need

X Dimension

CASE [pMeasureX]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Columns shelf

Y Dimension

CASE [pMeasureY]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Rows shelf

Edit both axes and remove the title from both. Right click on the word ‘Profit Ratio’ and Rotate Label. Right click on ‘X Dimension’ at the top and select hide field labels for columns. Right click on ‘Y Dimension’ on the left and select hide field labels for rows. Format to remove the row and column gridlines.

The State a user clicks on/selects needs to be captured within a parameter

pSelectedState

String parameter defaulted to New Jersey

We then need to determine if the State matches that selected

Is Selected State?

[State]= [pSelectedState]

Change the mark type to Circle, then add Is Selected State? to the Colour shelf and adjust to suit. Add a border to the mark too. Drag the ‘True’ value on the colour legend so its listed above ‘False’.

Add Is Selected State? to the Size shelf too, and adjust so the sizes are reversed, and alter the range.

Use the slider on the Size shelf too to make all the circles smaller too if need be.

Adjust the Tooltip so the text will be dynamic based on the measure selections too.

Then, modify the title of the sheet, so it too is dynamic.

Finally, this sheet too also needs to ensure the State circle clicked on doesn’t remain ‘selected’ so as above, create a True = TRUE and a False = FALSE calculated fields, and add these to the Detail shelf.

Adding the interactivity

Add both the sheets to a dashboard.

Create the following dashboard actions:

Set X Measure

A parameter action, that runs on Select of the Measure Selector sheet and passes Number (X) into the pMeasureX parameter.

Create a similar parameter action called Set Y Measure that passes Number (Y) into the pMeasureY parameter.

Create a dashboard filter action Selector Sheet – Unhighlight which on select of the Measure Selector sheet on the dashboard, targets the Measure Selector sheet directly, passing True = False into the filter. All values should show when the selection is cleared.

With these 3 actions, you should be able to test what happens when you click the different combinations.

Now we need 2 further dashboard actions

Set Selected State

A parameter action that on select of the Scatterplot passes the State field into the pSelectedState parameter.

Add finally, add another dashboard filter action, Scatter – Unhighlight that on select of the Scatter sheet on the dashboard, targets the scatter sheet directly, passing the fields True = False as the filter.

Hiding the Measure Selector

On the dashboard, select the Measure Selector sheet and select the Add Show/Hide Button from the context menu.

This will create a X button that can then be moved and positioned where required.

On the context menu of the X button, select Hide and the measure selector sheet will disappear, and the X will be replaced by a different image

Select Show from the context menu of this button, and the sheet will reappear. Test the functionality in presentation mode where simply clicking the button will invoke the show/hide behaviour.

And this should be the core functionality to demonstrate this measure swapping feature. My published viz based on this more basic solution is here.

If you’re aiming to build on the viz you’ve built over the last 3 weeks, then you need to replace any references you had to SUM([Sales]) and [Profit Ratio] in the various calculations with the X Measure and Y Measure fields accordingly. You’ll also need to adjust tooltips and titles of the various sheets using the X Dimension and Y Dimension fields as appropriate. My adapted solution built on from Kyle’s week 7 solution is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a reference line for each dimension?

Donna Coles1 Comment

Ann Jackson returned this week with a challenge primarily focussed on formatting.

The core requirement this week was to be able to present different measures on a chart, based on a user selection, but where the values displayed were of differing numerical formats

  • Sales per order in $ to 0 decimal places, formatted to show a ‘,’ every 1,000.
  • Profit Ratio as a % to 1 decimal place
  • Items per order as a numerical value to 2 decimal places

My focus points this week are

  • Measure swapping
  • Adding the line labels
  • Labelling the y-axis
  • Adding the reference lines
  • Building the blocks

Measure Swapping

This is technique that should be in everyone’s arsenal, as it’s a great way to present multiple views of the data without the need for multiple instances of the chart – it saves space and clutter but continues to allow flexibility.

The 3 measures required needed to be defined through calculated fields

Profit Ratio

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

Sales per Order

SUM([Sales])/COUNTD([Order ID])

Items per Order

SUM([Quantity])/COUNTD([Order ID])

A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below

SELECT A MEASURE

Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected

Display Measure

CASE [SELECT A MEASURE]
WHEN ‘Profit Ratio’ THEN ROUND([Profit Ratio]*100,1)
WHEN ‘Sales Per Order’ THEN ROUND([Sales Per Order],0)
WHEN ‘Items Per Order’ THEN ROUND([Items Per Order],2)
END

It’s within this field I chose to define the number formatting I wanted to display, and by then setting the number format of the field to Number Standard, it seemed to show what I intended on hover. Add Display Measure to Rows and plot against QUARTER(Order Date) coloured by Category to get the display below.

Adding the line labels

However while the numeric format is what’s required, I haven’t got the $ or % symbol, and I can’t apply that as part of the default formatting.

Instead I created explicit prefix & suffix fields

$ Label Prefix

IF [SELECT A MEASURE] = ‘Sales Per Order’ THEN ‘$’ END

% Label Suffix

IF [SELECT A MEASURE] = ‘Profit Ratio’ THEN ‘%’ END

Adding these 2 fields to the Detail shelf, they can then be referenced in both the Tooltip and the Label as follows

<$ Label Prefix><AGG(Display Measure)><% Label Suffix>

Based on the logic, either both fields will be NULL/blank else, only one will be populated, so you’ll never get $1,000% displayed!

Labelling the y-axis

Amend the y-axis to delete the Display Measure title, then add the SELECT A MEASURE parameter to the Rows shelf. Rotate and format accordingly.

Adding the Reference Lines

Adding a reference line – simples, surely! But why would Ann be making a challenge if it was that easy….hmmmmm! So what were the challenges posed here

  1. If you add an ‘Average’ reference line, you don’t get a value per line (even if you select the ‘per cell’ option) – you just get one average line. If the chart was split so there was a row per category, you’d be able to get this.

2.The lines displayed can’t be created via a ‘dual’ axis chart where the 2nd axis is showing the average, because the line format is a finely dotted line, and we can’t format a line mark this way. Proper reference lines can be formatted though, so I concluded the lines had to be true reference lines.

3. However, the labelling of a reference line is quite limited, and while I can show the value, I can’t use other calculated fields (ie the Prefix/Suffix fields) on the reference line label…

I came up with the following solution : create separate fields to store the AVG values for each Category, so that I could add 3 separate reference lines to the main chart; then create a dual axis line chart which also showed the average per category, label the line accordingly, and reduce the opacity of the line to 0%.

Ref Line Per Category

WINDOW_AVG([Display Measure])

Stores the average of the data displayed, and can be varied based on the table calc settings.

Ref Line – Tech

IF MIN([Category]) = ‘Technology’ THEN [Ref Line per Category] END

Only stores the average for the Technology data. I created equivalent ones of these for Ref Line – Office and Ref Line – Furniture

All 3 fields were added to the Detail shelf, then added as 3 different reference lines, coloured and formatted as a dotted line accordingly

To make the labels, I added Ref Line per Category to the Rows shelf to create a secondary axis. The table calculation was set as below

This produces a straight line for each category on a second chart, which I duly labelled by choosing to label the start of line, and aligning top left

I then set the opacity of the line colour to 0%, which makes the line disappear

I then set the chart to be dual axis, and synchronised the axis.

Building the blocks

For the block chart, I started by building a Tree Map (using Show Me) based on Category and the Ref Line Per Category fields.

I created a Rank field as

RANK([Ref Line per Category])

which I added to the Tooltip. I also then filtered the chart to Rank=1 to give me the main block. I then duplicated this sheet, and changed the rank filter to Rank=2, and repeated again for Rank=3. This gave me 3 sheets I could then organise onto the dashboard, as just using the tree map view directly, I couldn’t control how the different sections would display.

This was a fun challenge this week, slightly less taking than the previous weeks! My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a beautiful & dynamic bar chart?

Donna Coles1 Comment

Week 2 of #WOW2020 saw Ann post this challenge asking us to create a dynamic bar chart that changed both the measure being reported and the timeframe being reported over, based on user selections.

The measure needed to be displayed as the bar label, which needed to be formatted differently depending on whether it was Sales ($ 0 dp), Profit Ratio (% 1 dp) or Items Per Order (1 dp). Due to this, the label can’t be a single field, as it’s not yet possible in Tableau Desktop to apply number formatting within a calculated field *sigh*.

A couple of calculated fields are needed for the measures

Profit Ratio

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

and Items Per Order

SUM([Quantity])/COUNTD([Order ID])

The timeframe format displayed also needed to change based on whether the timeframe selected was Last 12 months (MMM’YY eg Apr’19), Last 13 Weeks (Week No) or Last 14 days (mm/dd eg 12/31).

The bar colour needed to reflect the measure selected, and measure selector needed to display a filled or open circle depending on whether the measure was selected or not.

Measure Selector

I realised there were a lot of similarities with the behaviour of the measure selector, and Luke’s challenge from the previous week. I’m not sure if this was intentional or not. As a consequence though, I’m not going to explain how I built the Measure Selector chart, as I utilised exactly the same principles for the Week 1 solution, and I’ve already blogged about it here.

Bar Chart

I built the bar chart on a single sheet, plotting a measure that could change based on selection, against a date field that also changed on selection.

To drive the selection change I needed 2 parameters:

Date Period

A string parameter containing the 3 values available

This parameter was just made available as a drop down for the user to select from on the dashboard.

Selected Measure

This was a string parameter that just stored a piece of text. It was set to the value ‘ Sales ‘ by default, but was changed via a Parameter Action following selection via the Measure Selector chart discussed above.

Note the leading & trailing space in the default value ‘ Sales ‘due to the alias that needs to be set – refer to my previous bog post to understand the relevance

The date field to plot then had to alter based on the Date Period parameter, so the following calculated field was created:

Order Date Truncate

DATE(CASE [Date Period]
WHEN ‘Last 12 Months’ THEN DATETRUNC(‘month’,[Order Date])
WHEN ‘Last 13 Weeks’ THEN DATETRUNC(‘week’, [Order Date])
ELSE DATETRUNC(‘day’, [Order Date])
END
)

This field is essentially storing the date as the 1st of the month, 1st day of the week or specific day level, so when plotted on a chart it can be set to ‘exact date’ and all the orders in the same month/week/day, are all grouped together.

The measure field to plot alters based on the Selected Measure parameter, so the following calculated field is required :

Measure to Show

CASE [Selected Measure]
WHEN ‘ Sales ‘ THEN SUM([Sales])
WHEN ‘ Profit Ratio ‘ THEN [Profit Ratio]
WHEN ‘ Items Per Order ‘ THEN [Items Per Order]
END

Plotting Order Date Truncate on columns as a discrete exact date against Measure to Show on rows with mark type set to Bar, and you can use the parameter to change the number of bars.

But this is showing all the months/weeks/days, so we need to add a filter to restrict to amount of information showing.

Dates to Include

CASE [Date Period]
WHEN ‘Last 12 Months’ THEN [Order Date] >= DATEADD(‘month’,-12,[Today])
WHEN ‘Last 13 Weeks’ THEN [Order Date] >= DATEADD(‘week’,-13,[Today])
ELSE [Order Date] >= DATEADD(‘day’,-14,[Today])
END

This will true or false, and is added to the Filter shelf set to True

Note – [Today] is another parameter that is defaulted to 01 Jan 2020 as per Ann’s requirement.

The field names for the Order Date Truncate isn’t displaying as required though. To fix this, I created

Label:Date

CASE [Date Period]
WHEN ‘Last 12 Months’ THEN LEFT(DATENAME(‘month’, [Order Date Truncate]),3) + ”” + RIGHT(STR(YEAR([Order Date Truncate])),2)
WHEN ‘Last 13 Weeks’ THEN ‘Week ‘ + STR(DATEPART(‘week’, [Order Date Truncate]))
ELSE STR(DATEPART(‘month’,[Order Date Truncate])) + ‘/’ + STR(DATEPART(‘day’, [Order Date Truncate]))
END

which is building the required string to display.

Add this to the Columns shelf next to the Order Date Truncate field, then untick Show Header against the Order Date Truncate field

Next up is the labels to display on top of each bar. This also needs to show something different depending on the measure selected. For this I built 3 calculated fields:

Label: Sales

IF [Selected Measure] = ‘ Sales ‘ THEN [Sales] END

which is formatted to $ with 0 decimal places

Label: Profit Ratio

IF [Selected Measure] = ‘ Profit Ratio ‘ THEN [Profit Ratio] END

which is formatted to % with 1 decimal place, and

Label: Items to Order

IF [Selected Measure] = ‘ Items Per Order ‘ THEN [Items Per Order] END

which is formatted to 1 decimal place

These 3 fields are then added to the Label shelf, and the label text is then edited to ensure all the fields are positioned side by side. Due to the fact only 1 of these fields will ever contain data, it looks as though there is only 1 label showing

Next we need to colour the bars, which is based on Selected Measure which is just added to the Colour shelf. When it’s added though, you only get a single measure in the colour legend at a time, so to set the colours you need to set Selected Measure to be each option of Sales, Profit Ratio, Items to Order (based on what the alias has been set though), either by manually typing into the parameter, or selecting a measure on the dashboard via the Measure Selector chart you built initially.

The tooltip is then set as follows

and once again the Label:xxx measures are all positioned alongside each other as, remember, only one will ever contain any data.

Finally it’s just a case of tidying up the chart – remove the rows & columns, the Measure to Show axis, and the Label:Date label.

The zero line of the rows is set to be a thicker grey line to get the desired display

Once done, the bar chart can be added to the dashboard, and if the Parameter Action on the Measure Selector chart has been set up properly, the bar chart display should change as that chart is interacted with.

My published version of the challenge is here.

Happy vizzin!

Donna