Tag: Week 17

Can you swap measures?

Donna Coles2 Comments

It’s back to the EFL this week for my #WOW challenge. Once again I’ve tried to provide a multi-level challenge – a version that uses some core skills and features, and a version that just pushes the display a bit further, just for fun. I’ll start with the core build and then use that as the base for the bonus challenge.

Setting up the parameters

The main crux of this challenge is measure swapping, so for this we need a parameter

pMeasure

integer parameter listing values 1 to 4 which are aliased as per the screen shot below. Default value is 1 (Points).

Note – you can use a string parameter with the actual words. I just chose to use this method to demonstrate the aliasing ability. Also when referencing this parameter in a CASE statement (which we’ll do shortly), using integer values for comparisons is slightly more efficient than string comparisons.

We also need the user to have the ability to select the team they want to track

pSelectedTeam

string parameter defaulted to your preferred team (I chose Chelsea). This is a List parameter that is populated from the Team field via the Add values from button

Building the calculations

We need to determine the measure to display based on the parameter selection

Measure to Display

CASE [pMeasure]
WHEN 1 THEN SUM([Cumulative Points])
WHEN 2 THEN SUM([Cumulative Goal Difference])
WHEn 3 THEN SUM([Cumulative Goals For])
WHEN 4 THEN SUM([Cumulative Goals Against])
END

We also will need to colour the bars based on the selected team

Is Selected Team

[Team]=[pSelectedTeam]

and we need to sort the data based on best to worst, but need to consider that for Goals Conceded, the higher the value the worse the team is.

Sort Measure

IF [pMeasure] = 4 THEN [Measure to Display]*-1 ELSE [Measure to Display] END

We only want to show teams once they start participating in a Season. For this, we need to identify the team’s 1st season in the EFL

1st Season per Team

{FIXED [Team]: MIN(IF [Cumulative Points] > 0 THEN [Season End Year] END)}

and then we can create a field we can filter on, based on this

Show Team

[Season End Year]>=[1st Season per Team]

We only want to show a label against the 1st team and the selected team, so create

Label to display

IF MIN([Is Selected Team]) OR FIRST()=0 THEN [Measure to Display] END

and finally, we need to display the value of the current season’s measure on the tooltip, as well as the cumulative value, so we need another case statement

Tootltip Measure

CASE [pMeasure]
WHEN 1 THEN SUM([Points])
WHEN 2 THEN SUM([Goal Difference])
WHEn 3 THEN SUM([Goals For])
WHEN 4 THEN SUM([Goals Against])
END

Building the core bar chart

On a new sheet, add Team to Rows and Measure to Display to Columns. Add Season End Year to Filter and select 1993. Add Show Team to Filter and select True. Apply a sort to the Team field to sort by the field Sort Measure descending.

Add Is Selected Team to Colour and adjust accordingly. Add Label to display to Label. Add Season and Tooltip Measure to Tooltip and update accordingly,

Show the pMeasure and pSelectedTeam parameters and the Season End Year Filter. Adjust the Season End Year filter control so that the All option isn’t available and it displays as a single value slider control.

Move the Season End Year filter control on by one value and notice how the chart transitions. Adjust the Animations settings (Format menu > Animations) to be sequential and slow

Finally tidy up the display by

  • hiding the Measure to Display axis (right click > uncheck show header)
  • hiding the Team row label (right click > hide field labels for rows)
  • widen each row a bit
  • hide gridlines, zero lines, axis rulers and axis ticks
  • add pale grey row dividers
  • set the background colour of the worksheet
  • adjust the font of the row labels – I used Tableau Book 8pt in colour #3e1756 (dark purple)

Name the sheet Core or similar and add to a dashboard setting it to Fit Entire View

Add a title to the dashboard that references the pSelectedTeam parameter.

My core viz is here.

Building the bonus viz

Start by duplicating the core viz sheet.

We’re going to use a gantt bar to simulate a central line for each row. This bar needs to extend to the largest measure in the table for every row, so this is the point we’ll plt

Max Measure

WINDOW_MAX([Measure to Display])

Add this to the Columns shelf, and on the Ma Measure markls card that gets added, remove the Is Selected Team from the Colour shelf, and change the mark type to gantt bar.

The bar needs to extend to the 0 or the minimum value in the window (if its less than 0), so we need a field to show the difference between the max and the minimum of 0 or the ‘window min’, but we need to multiple by -1 so the size extends in the right direction.

Max-Min Diff

([Max Measure] – MIN(WINDOW_MIN([Measure to Display]),0)) * -1

Add this to the Size shelf, and then update the size to be as small as possible. Remove Label to Display from the marks card and add a border the same colour as the background to the Gantt bar (via the Colour shelf) to make the line very narrow.

Add Team to the Label shelf and align centre left. Format the label text to be 8pt and dark purple. Then make the chart dual axis and synchronise the axis.

Right click the top axis and select move marks to back. Then hide both axis (right click, uncheck show header) and hide the Team pill on Rows too (again uncheck show header)

Verify the Tooltip on the gantt bar displays the same as on the main bar. If not add the relevant fields and adjust to match.

Tidy up the formatting by removing the row and column dividers. If need be adjust the colour of the Gantt bar to be a paler grey.

Change the measure value to Goal Difference and adjust the year to 2024 and check the display looks as expected, especially at the bottom – the gap between the label of the team at the bottom and the bar is minimal.

Add the chart to a dashboard – the simplest way is to duplicate the core dashboard and then use the swap sheets feature to quickly swap the main vizzes.

My published viz is here

Happy vizzin’!

Donna

How far would total steps from TC23 respondents take you?

Donna ColesLeave a comment

As community month draws to a close, long term participant Deborah Simmonds set us a map based challenge based on TC23 data.

I chose to use the data that was associated to the solution workbook, rather than take the direct source, so bear that in mind if you’re following along.

Examining the data

As there were quite a few fields in the data set which were directly related to the solution, I just wanted to familiarise myself with what I was working with initially. Essentially we have a row per person (Names Full) with the hotel they stayed at (Label Hotels), and how many steps (Steps) they took each day (Date). The details of the convention centre (Label Convention Centre) exist against every row.

The LAT and LON fields contain the location details of the hotels, while Convention LAT and Convention LON contain the location details of the Mandalay Bay Convention Centre.

Names Full already had the logic applied to create names for users based on their User ID if the Name was NULL, so I didn’t need to do anything for that requirement.

We only want to consider records relating to those people who attended ‘In Person’ and who had provided a hotel. Consequently I added the following as data source filters (right click data source -> edit data source filters) to exclude unrequired records from the whole analysis.

  • Did you attend in person or virtual? : In person
  • Label Hotels : excludes NULL

Building the BANs

The BANs have 3 metrics – the number of attendees, the number of steps, and the distance in metres (based on the logic that 1 step = 0.75m).

Attendees

COUNTD([User ID])

Distance (m)

[Steps] * 0.75

On a new sheet add Date to Filter as a range of dates and show the filter control. Add Attendees, Steps and Distance (m) so the measures are displayed in a row.

Change the mark type to shape and add a transparent shape (see this blog for more details). Add Measure Names to Label too and then adjust label accordingly and align centrally.

Hide the header row (uncheck show header), remove row dividers and don’t show tooltips. Name the sheet BANs or similar. Adjust the date slider and the values should adjust.

Building the bar chart (viz in tooltip)

On a new sheet add Names Full to Rows and Steps to Columns. Apply the same Date filter (from the BANs worksheet, set the filter to apply to this new worksheet too).

The viz needs to display a reference line showing the overall average of the steps per person across the selected dates, regardless as to whether the records are filtered to a hotel or not.

What do I mean by this… well if I add a standard average reference line to the viz above, the average for the whole table is 78.2k steps.

If I now filter this by a Label Hotel, then the average changes, and I don’t want that – I still want to see 78.2k.

But the average does need to change if the date range changes

This took a bit of effort to get right, but I needed

Avg Steps

SUM({FIXED Date: SUM([Steps])})/SUM({FIXED:COUNTD([User ID])})

Format this as a number with 1dp set to the K (thousandths) level

and I also needed to add the Date field on the Filter shelf to context.

So reverting back to the initial view of the bar chart… right click on the Date filter and Add to Context. Add Avg Steps to the Detail shelf. Then add a reference line (right click the Steps axis > add reference line) that displays the Avg Steps with a dark dashed line with a custom label.

Format the reference line to position the label at the top and adjust the font style.

To colour the bars we need

Steps above average

SUM([Steps]) >=[Avg Steps]

Add this to the Colour shelf, change the colours and adjust the opacity to about 75%.

Hide the column heading, and adjust the font size of the names and the axis. Name the sheet Bars or similar.

Building the initial map

I decided to create 2 maps – one for the initial display of all the hotels and the convention centre, and then one for the selected location and buffer.

To plot the hotels on the map I created

Hotel Locations

MAKEPOINT([LAT],[LON])

On a new sheet, add this to the Detail shelf. A map will automatically generate with Latitude and Longitude fields. Change the Mark Type to Circle, then add Label Hotels to Label and align left middle. Edit the map Background Layers (via the map menu) to add Streets, Highways etc to the display

Add Steps, Attendees and Distance(m) to the Tooltip. Add Attendees to the Size shelf and adjust the size to vary by range

Apply the Date filter from the other worksheets to this sheet too. For the tooltip, We need to know about the min and max dates in the range selected. Create

Min Date

MIN([Date])

and custom format simply as dd (the day only)

Also create

Max Date

MAX([Date])

and custom form this as dd mmm yyyy

Add both of these fields to the Tooltip shelf. Adjust the text in the Tooltip and add a reference to the Bars sheet as a viz in tooltip (Insert > sheets > ). Adjust the height of the sheet to be 900.

The viz in tooltip should now display nicely on hover

To add the mark for the convention centre, we need

Conf Location

MAKEPOINT([Convention LAT], [Convention LON])

Drag this onto the map, and drop it when Add A Marks Layer displays

This will create a 2nd marks card. Change the mark type to shape and select the Tableau sparkle image if you have it stored. If not, just use another shape or circle (coloured differently).

Add Attendees to Size and add Label Convention Centre to Label and align left middle. Add Steps and Distance(m) to Tooltip and adjust to suit. Hide all the map options (map menu -> map options -> uncheck all the selections). Name the sheet Map – Initial or similar.

Building the ‘selected’ map

This will use parameters to identify what’s been selected – a hotel or the convention centre, so we need

pSelectedHotel

string parameter defaulted to empty string

and

pSelectedCentre

string parameter defaulted to empty string, just like above

The intention is that either both these parameters will be empty or only one will be populated.

To plot on a map we need

Selected Hotel Location

IF [pSelectedHotel] = [Label Hotels] AND [pSelectedCentre] =” THEN [Hotel Locations] END

and

Selected Centre Location

IF [pSelectedHotel] = ” AND [pSelectedCentre] =[Label Convention Centre] THEN [Conf Location] END

Duplicate the initial map sheet and name it Map – Selection. Show the two parameters and verify both are empty.

On the Hotel Locations marks card, drag the Selected Hotel Location field and drop it straight onto the Hotel Locations field. On the Conf Location marks card, drag Selected Centre Location and drop straight onto the Conf Locations field. Your map shouldn’t display anything…

Manually type ‘Luxor’ into the pSelectedHotel parameter. A mark should display. Adjust the Label of the Selected Hotel marks card so it is larger font, and aligned top middle. Set the colour to orange and remove the halo.

Remove the text from the pSelectedHotel parameter and manually type ‘Mandalay Bay Convention Centre’ into the pSelectedCentre parameter. A mark should display. Adjust the Label of the Selected Centre marks card so it is larger font, and aligned top middle. Remove the halo from the Colour shelf.

To create the buffer circle, we need to define the buffer radius, which is the attendee steps in metres.

Buffer Distance (m)

{FIXED : SUM(IF [pSelectedHotel] = [Label Hotels] OR [pSelectedCentre] = [Label Convention Centre] THEN [Steps] END)} * 0.75

and then we create

Buffer

IF [pSelectedHotel] <> ” THEN
BUFFER([Selected Hotel Location], [Buffer Distance (m)], ‘m’)
ELSEIF [pSelectedCentre] <> ” THEN
BUFFER([Selected Centre Location], [Buffer Distance (m)], ‘m’)
END

Add this as another marks layer.

Reduce the opacity on the colour shelf to 0% and set the border to be orange. We don’t want to allow any interactivity with the buffer, so disable selection of the layer, and also move down so it is listed at the bottom of the 3 map layers.

Show the Date filter control and adjust the dates to see the buffer adjusting. Test the behaviour with a hotel too (you may find you want to add some more detail to the background layers of the map).

We will use dynamic zone visibility on the dashboard to decide whether to display the initial or the selected map. To control this, we need

Show Initial Map

[pSelectedCentre]=” and [pSelectedHotel]=”

and

Show Selected Map

[pSelectedHotel]<>” OR [pSelectedCentre]<>”

Adding the interactivity

Create a dashboard, add the BANs and both the map sheets.

Create a dashboard parameter action

Set Hotel

On selection of the Initial Map set the pSelectedHotel parameter passing in the value from the Label Hotels field. When the selection is cleared, reset to ”.

and another parameter action

Set Conv Centre

On selection of the Initial Map set the pSelectedCentre parameter passing in the value from the Label Convention Centre field. When the selection is cleared, reset to ”.

Select the Initial Map object, and from the Layout tab, set the visibility to be controlled by the Show Initial Map field

Then select the Selected Map object, and set the visibility to be controlled by the Show Selected Map field. Only one of the maps should display based on the interactivity.

This is all the core functionality of the map, but Deborah threw in a couple of extra asks…

Building the Distance Legend

We’re using map layers again for this. Create a new field

Zero

MAKEPOINT(0,0)

Add it to the Detail shelf of a new worksheet to create the 1st map layer, then immediately add another map layer, by adding another instance of Zero to the sheet.

Switch the axis, and the map will disappear, and you’ll have axis displayed instead. Change the mark type of the first map layer to circle and colour orange.

Change the mark type of the 2nd map later to circle and colour pale grey with an orange border. Increase the Size of this circle so it appears as a ring around the filled orange circle. Move the 2nd marks layer down to the bottom and disable both marks from being selectable.

Hide the axis and gridlines/zero lines.

Right click on the central circle and annotate point. Don’t enter any text into the annotation box, just click OK. You should get the annotation box with a line.

Move the box so the connector line is horizontal, then format the annotation so the shading is set to none and the line is formatted to be a darker dashed line. Update the title of the sheet.

Building the Size Legend

Apply a similar process to that described above, but this time create 3 mark layers where the mark type is an open circle shape which is coloured blue, and for each layer, the size of the circle is slightly bigger. This time show the zero lines.

Set the background of both the legend sheets to be none (ie transparent), then add them as floating objects onto the dashboard. Use the control visibility feature to only display the Size legend when Show Initial Map is set, and only display the Distance legend when Show Selected Map is set. Set a background against each object of light grey, that is then set to 80% transparency.

With this you should have a completed challenge. My published version is here.

Happy vizzin’!

Donna

Custom Map Analysis

Donna Coles1 Comment

For the final week of Community Month, my colleague, Nik Eveleigh, posed this challenge to apply some tricky filters to a map. Let’s dive straight in!

Identifying the States and Profitability

The core functionality of the map display is driven by a parameter allowing the user to select which set of states they want to analyse, so let’s set this up first.

Select a grouping

string parameter containing 6 entries in the list and defaulted to ‘Top 20 States by Sales’

To identify the top 20 States by Sales, we need to create a Set. Right click on State/Province > Create > Set and use the ‘Top’ option to create the set based on top 20 sum of Sales.

Top 20 States by Sales

To verify this is working as expected, add State/Province to Rows, Sales to Text and sort descending. The add Top 20 States by Sales to Rows and you should see the first 20 rows with In and the rest listed as Out.

To identify the states selected when the ‘Custom States List’ option is selected, we’ll also need another set to store the selections. Right click on State/Province > Create > Set, leave the list of states all unselected and rename the set

Selected States

To verify this is working, on a new sheet add State/Province to Rows and Selected States to Rows too. Then on the context menu of the Selected States pill, choose Show Set to get the list of States displayed. Select the first couple in the list and see the value in the table change from Out to In.

It is this set control filter list that will be used to provide the selection when the appropriate value in the Select a grouping parameter is chosen.

While the sets display In or Out when shown in the table, they are actually booleans with the equivalent of a True or False. We now need to build a boolean field which will encapsulate all the relevant states included based on the parameter option selected.

Filter States

CASE [Select a grouping]
WHEN ‘Top 20 States by Sales’ THEN [Top 20 States by Sales]
WHEN ‘Central Region’ THEN IF [Region] = ‘Central’ THEN TRUE ELSE FALSE END
WHEN ‘Southern Region’ THEN IF [Region] = ‘South’ THEN TRUE ELSE FALSE END
WHEN ‘Eastern Region’ THEN IF [Region] = ‘East’ THEN TRUE ELSE FALSE END
WHEN ‘Western Region’ THEN IF [Region] = ‘West’ THEN TRUE ELSE FALSE END
WHEN ‘Custom States List’ THEN [Selected States]
END

Let’s sense check the workings of this too. On a sheet add State/Province, Region and Filter States to Rows. Show the Select a grouping parameter. Also click on the Selected States field in the left hand data pane and Show Set, so the list of states shows. Ensure all are unchecked. Depending on the option selected in the Select a grouping parameter, then Filter States column should display true or false.

If you select a state from the list, this should only present as True when the Custom States List option is selected

Right, now we know how to identify the states we want, we can start to look at understanding their profitability. Firstly we need to get the profit ration

Profit Ratio

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

format this to % with 0 dp.

Profitability

IF ATTR([Filter States]) THEN

IF [Profit Ratio] < -0.25 THEN ‘Highly Unprofitable’

ELSEIF [Profit Ratio] >= -0.25 And [Profit Ratio] < 0 THEN ‘Unprofitable’

ELSEIF [Profit Ratio] >=0 AND [Profit Ratio] < 0.25 THEN ‘Profitable’

ELSE ‘Highly Profitable’

END

ELSE ‘Not Included’
END

If the states is one of the filtered ones then work out isn’t profitability ‘bracket’ otherwise report as ‘Not Included’.

Building the Map

On a new sheet double-click on State/Province to automatically generate the map. If the map doesn’t display with US and Canada Edit Locations (via the Map menu) and ensure your settings are as below

Display the Show a grouping parameter and have it set to Top 20 States by Sales. Add Profitability to Colour and adjust colours accordingly.

Click on the Selected States field in the left hand data pane and Show Set, so the list of states shows, then test changing the values in the Select a grouping parameter and see the display change.

Clean the map up by clicking Map -> Background Layers, and then unchecking all the options in the Background Map Layers section displayed on the left hand side.

To label the highlighted state we need

Label – PR

IF ATTR([Filter States]) THEN [Profit Ratio] END

format to % with 0 dp and then add to the Label shelf and set the font to bold.

Add Profit Ratio to the Tooltip shelf and adjust the tooltip.

To display the summary Profit Ratio values for all the filtered states vs those not selected, we need

PR by Filter State

{FIXED [Filter States]: [Profit Ratio]}

and then

PR Selected States

ZN({FIXED:AVG(IF [Filter States] THEN [PR by Filter State] END)})

Custom format this with 0%;-0%;–

This formatting with show values with 0dp for positive and negative values and — when no values exists.

Repeat the process to create

PR Non Selected States

ZN({FIXED:AVG(IF NOT([Filter States]) THEN [PR by Filter State] END)})

and apply the same formatting above.

Add both PR Selected States and PR Non Selected States to the Detail shelf and change the aggregation to Average.

Then click on a state on the bottom left of the map (I chose California) and select Annotate > Mark. Add the reference to the PR Selected States and supporting text into the annotation dialog. The when completed, manually move the annotation to the space to the left of the map. Format the annotation to add a border, round the edges and remove the line. You many need to re-edit the annotation to rec-centre the text.

Repeat a similar process, by annotating a state on the right hand side and referencing the PR Non Selected States field instead.

Finally remove all row & column dividers and hide the map options (Map -> Map Options -> uncheck all selections)

Hiding the Selected States control

In order to control visibility of the Selected States list, we need a boolean field

Custom States Selected

[Select a grouping] = ‘Custom States List’

Once all objects have been added to the dashboard and arranged where you want, click on the Selected States control, so it is selected via a grey border, then on the left hand Layout pane, select the Control visibility using value checkbox and choose the Custom States Selected field

The State list will now only display when the Select a grouping parameter contains the ‘Custom States List’ value.

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

Happy vizzin’!

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

Generation Population

Donna ColesLeave a comment

For week 17 of #WOW2021, Sean Miller decided to challenge us with recreating a chart by Nathan Yau (see here for the original). The aim was to recreate within 1 sheet, which I managed to do. So how did I do it? Read on 🙂

  • The groundwork
  • Colouring the bars
  • Adding the year labels
  • Final formatting

The groundwork

The chart itself follows a straight forward structure of multiple blue dimension fields on Columns with a green measure on Rows similar to this view below based on Superstore Sales data – it’s just formatted a bit more creatively!

The data provided just contains 3 fields : SEX, AGE and 2019 Population. We want to present the 2019 Population measure for the Total SEX only by Year. We need to create the Year field, which is simply

Year

2019-[AGE]

I dragged this into the ‘dimensions’ section of the data pane (above the line).

This allows us to create the basic bar chart required

We now need to define the various fields that we will need to add as additional dimensions on the Columns shelf to create the ‘generation’ data panes.

Generation

IF [Year]<= 1927 THEN ‘Greatest
Generation’
ELSEIF [Year]<= 1945 THEN ‘Silent Generation’
ELSEIF [Year]<=1964 THEN ‘Baby Boomer’
ELSEIF [Year] <= 1980 THEN ‘Generation X’
ELSEIF [Year]<=1996 THEN ‘Millennials’
ELSEIF [Year]<=2012 THEN ‘Generation Z’
ELSE ‘Gen
Alpha’
END

NOTE – there is a deliberate carriage return in the condition for ‘Greatest Generation’ and ‘Gen Alpha’ which will force the field to ‘wrap’ when displayed.

Having defined the above, we need to determine

Total Population Per Generation

{FIXED [Generation], [SEX]: SUM([2019 Population])}

and then

% of Total Population

SUM([Total Population Per Generation]) / TOTAL(SUM([Total Population Per Generation]))

NOTE – to create this field, I originally created a ‘quick table calculation’ against the Total Population Per Generation field which I’d displayed on a view, and then dragged the resulting pill into the measures pane to create the new field with the desired calc.

Let’s put these in a table, so we can then check the values, and see that the 2nd and 3rd columns are the same value for each row associated to a particular generation, which is what we need.

Right, so now we need to determine the rank based on the Total Population Per Generation

Rank

RANK_DENSE(SUM([Total Population Per Generation]))

Format this to a custom number with 0 decimal places, but prefixed with #

When added to the table we get

The intention, is that Rank will be displayed as discrete ‘header’ pill rather than a measure, so let’s move Rank to be the 1st pill on the Rows shelf and change to be discrete.

But we need the Total Population Per Generation and % of Total Population fields to be combined into a single pill. So we need to do a bit of string manipulation/ number formatting for this

Total | Percent

STR(ROUND(SUM([Total Population Per Generation])/1000000,1)) + ‘M’ + ‘ | ‘ + STR(ROUND([% of Total Population] * 100,1)) +’%’

This looks complicated, but its because even though you may have applied the relevant display number formatting against the individual numeric measures of Total Population Per Generation and % of Total Population, the formatting is not preserved, when converted into a string field, which this field needs to be. So the relevant calculations need to be applied within the field itself.

This outputs the below

Now we need a way to sort the data so the ‘Greatest Generation’ associated to the earliest years is listed first. I did this by determining the minimum date within each Generation.

Min Year Per Generation

{FIXED [Generation], [SEX]: MIN([Year])}

Add this into the view as the first pill in Rows, and the data should automatically sort from lowest to highest

We can now build the viz – duplicate the table sheet, remove Total Population Per Generation and % of Total Population from the Measure Values section. Drag 2019 Population to Columns, then click the swap rows & columns button :

Colouring the bars

The bars are coloured based on each ‘Generation’ pane. You could hardcode this along the lines of ‘Generation = x OR Generation = y or Generation = z etc’ where x, y and z etc are generations of the same colour. This would return true or false, which you can then add to the colour shelf and adjust accordingly.

I decided to be a bit more dynamic, deciding I wanted to set the colour based on whether it was an odd or even pane.

For this I created another ‘rank’ field based on the field I’d used to ‘sort’ the data, the Min Year Per Generation field.

Sort Position

RANK_DENSE(MIN([Min Year per Generation]),’asc’)

If you add this into the data table, you’ll see each section is numbered 1 -7

From this, we can then determine if the number is even (or not)

Sort Position is even number

[Sort Position]%2=0

Add this onto the Colour shelf which will return True or False and colour accordingly.

Adding the year labels

The year labels are achieved by using a dual axis chart, to plot a point for each specific year (based on the Min Year Per Generation field) at some arbitrary value.

Point to Plot Year Label

IF [Year]=[Min Year per Generation] AND [Year]<>1919 THEN 4700000 END

For each ‘min year’ that isn’t 1919, plot a value at 4.7M.

Add this field to the Rows shelf, change mark type to circle, reduce size to as small as it can, and set the colour transparency to 0.

Add Min Year Per Generation to the Label shelf, then change the alignment to vertical.

Now you make the chart dual axis and synchronise the axis. Some of the colours/marks may change, so reset by removing Measure Names from the Colour shelf and changing the mark types bar to bar & circle.

Final formatting

So at this point all the main components are there. It’s now a case of formatting – removing right and bottom axes, removing gridlines. The vertical dashed lines, are column dividers, set at the pane level only.

The solid left hand axis is set via

The text is formatted using the fonts advised in the requirements and sizes adjusted to suit.

Add on a tooltip, and set the background colour of the worksheet and you should be done.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you calculate weekday run rate?

Donna ColesLeave a comment

This week’s #WoW2020 challenge was set by Meera Umasankar who once again was tackling the concept of ‘run rate’, but this time with an added twist – only consider the working days (ie the typical Mon-Fri weekdays), rather than every day in the month, the assumption being, this ‘business’ does not trade on weekends. Following on from last week’s challenge, Meera also chose to include a bit of blending to combine actual orders against the plan/target. For this Meera provided a custom dataset which just included a sheet of Actuals by Region for each day in April 2020 up to 23rd April, and Plan by Region for the whole month of April.

As per usual I started by putting together a table of data with the core numbers I was going to need per region : MTD value, Run Rate value, Plan value.

Building the key data fields

Whilst Meera had provided data just for April up to April 23rd, I decided to build this in a way as if the data could change.

Today

{FIXED: MAX([Date])}

This stores the maximum date from the Actuals data source – ie 23 April 2020.

Current Month Only

[Date]>=DATETRUNC(‘month’, [Today]) AND [Date] <= [Today]

When true, this will just consider the records in the Actuals data source that are dated between 1st April & 23 April. As it happens, due to the data provided, this will be everything, but in a typical business situation, you’re actuals would probably contain previous months data too.

MTD

IF [Current Month Only] THEN [Sales] END

Only stores the sales for the month we want to report on.

To get the plan we need to blend to the Plan data source. As the data in the Actual data source is per day, and the Plan is per month, we need to blend the data at the month level. Whilst this can be set in other ways, I like to be explicit when using blending, so in my Actuals data source I created

BLEND – Date

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

This stores the 1st day of the month (1st April 2020) against every row of data.

In the Plan data I created a similar field, which is just essentially a duplicate field of the existing Date field, but by having the same name, it allows the blend joins to be automatically picked up.

Blend – Date

[Date]

Ok, let’s get these 2 measures on a table, to sense check we have the right figures so far :

  • Add Region to Rows (from the Actuals data)
  • Add MTD to Text
  • Add Plan to Text (from the Plan data)
  • Ensure the blend join links on both Region and BLEND – Date are clicked (due to the minimal data we have, the blend on Region only will work, but it’s good practice to include the date blend too if the Plan data contained different months).
  • Apply formatting as required to the MTD & Plan numbers

Calculating the Run Rate

Meera defines the Run Rate as being the value of Sales expected to be received in the whole month (the end of month position/forecast), based on the rate of sales so far in the month. So we’re looking to work out average sales made per day, then extrapolate that across the number of days in the month.

However, the twist in this challenge, is to only give consideration to the number of weekdays (ie working days).

As with many things, I chose to use my best friend ‘Google’ to see if it would throw up anything that may help this requirement, and it did, very quickly. There is an existing Tableau KB article that describes exactly how to work out the number of weekdays between 2 dates. You can find it here.

To work out the Run Rate I need

  • to work out the average Sales per weekday so far
  • multiply that by number of weekdays in the month

So I need to work out the number of working days between 1st April and 23rd April, and also the number of working days between 1st April & 30th April. I need a fair few calculated fields for all this, which I’ll build up rather than combine altogether.

Start of Current Month

DATETRUNC(‘month’,[Today])

simply truncates to 1st of month.

End of Current Month

DATEADD(‘day’, -1, DATEADD(‘month’, 1, [Start of Current Month]))

Adds 1 month onto start of month, then takes off 1 day to get the last day in the month

Following the steps in the article, I need to adjust these dates if they happen to fall on a weekend.

Start of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [Start of Current Month]) = 1
THEN DATEADD(‘day’, 1, [Start of Current Month])
ELSEIF DATEPART(‘weekday’, [Start of Current Month]) = 7
THEN DATEADD(‘day’, 2, [Start of Current Month])
ELSE [Start of Current Month]
END

If the Start of Current Month lands on a Saturday or a Sunday, the start is shifted forward to the following Monday.

End of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [End of Current Month]) = 1
THEN DATEADD(‘day’, -2, [End of Current Month])
ELSEIF DATEPART(‘weekday’, [End of Current Month]) = 7
THEN DATEADD(‘day’, -1, [End of Current Month])
ELSE [End of Current Month]
END

If the End of Current Month lands on a Saturday or Sunday, the end is shifted back to the previous Friday.

#Weekdays in Month

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)]) + 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)])))

This is working out the number of days between the adjusted start & end dates, then adding 1 to this number. It then works out the number of weeks between the adjusted start & end dates, multiples by 2 (since in every week there are 2 weekend days), and then this number is subtracted from the first.

We then need to repeat this to work out the working days from start to today.

Today (shift to weekday)

IF DATEPART(‘weekday’, [Today]) = 1
THEN DATEADD(‘day’, -2, [Today])
ELSEIF DATEPART(‘weekday’, [Today]) = 7
THEN DATEADD(‘day’, -1, [Today])
ELSE [Today]
END

This is our end date, so the date is once again shifted back to the previous Friday if it happens to be a Saturday or Sunday.

# Weekdays from start to Today

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])+ 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])))

Now we have these values, we can work out

Run Rate

(SUM([MTD])/[# Weekdays from Start to Today]) * [# Weekdays in Month]

Format this and add to check table

Building the chart

The left hand side of the chart is all text, but to present it as required, we need a fake axis.

  • From the Actuals data source, add Region to Rows
  • In the Columns shelf type in MIN(0) to create the fake axis.
  • Change the Mark Type to Text
  • Add Region, MTD, Run Rate from the Actuals data source to Text shelf
  • Add Plan from the Plan data source to the Text shelf (don’t forget to check the blend links)
  • Make each row bigger if everything all seems a bit squashed.

We’ll come back to formatting these fields later. Let’s now get the bar displayed & target displayed. This is a dual axis combining a bar and a gantt chart. Add to the chart as follows

  • Add Run Rate to Columns
  • Change the Mark Type of this measure to Bar
  • Remove all fields apart from Run Rate from the Label shelf of this card
  • Change Alignment of the Label to be left aligned
  • Add Plan to Columns (check the blend links)
  • Change Mark Type to be Gantt and remove fields from Label shelf of this card
  • Set to Dual Axis and Synchronise Axis.
  • Remove Measure Names from Colour shelf of the Bar and Gantt marks cards
  • Change Colour of the Gantt Bar (Plan) to black and add a black border to make it a bit thicker
  • Turn off Tooltips on All marks cards.

Indicating if Plan isn’t going to be met

The bar chart should be red if the Run Rate is less than Plan. The Run Rate on the Text side should also be displayed in red too if it doesn’t meet and black otherwise. We’re going to need some additional fields for this.

Run Rate < Plan

[Run Rate] < SUM([Plan (2020_04_22_WW17_Sales Projection)].[Plan])

Add this to the Colour shelf on the Bar marks card, and adjust the True/False colours accordingly

We can’t conditionally format an individual field in a Text display, so we need to create 2 further instances of the Run Rate field, where only one will ever display.

Run Rate < Plan (red)

If [Run Rate < Plan] THEN [Run Rate] END

Run Rate > Plan (black)

If NOT([Run Rate < Plan]) THEN [Run Rate] END

Format these accordingly, then add to the Text shelf of the Text marks card. Remove the original Run Rate field. You should still only have 1 run rate value displayed per row.

Now we can tidy up the display of this text. Ensure the Run Rate < Plan (red) and Run Rate > Plan (black) fields are on the same line of text with no spaces between, then colour the fonts to match the requirements

Finally, remove axis/row headers, tidy up gridlines etc, and adjust the width of the bars to suit.

Title Sheet

As the title needs to include the date, and to ensure it would be dynamic, I created a simple text sheet to display the title, and set the worksheet background to a light grey.

I then added both sheets to a dashboard, with both set to ‘Fit Entire View’, and titles hidden.

To get the Phone Layout display, I then selected the Phone option, clicked the padlock to Edit layout, and set to Fit all, and made adjustments to suit. The issue you might have though is that while things all look a bit squashed on your laptop display, it actually will render ok when published. This can unfortunately be a bit of trial & error.

My published viz is here.

Happy vizzin’ and stay safe!

Donna