Can you create a waffle chart?

Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.

You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.

Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.

Building the waffle

Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field

Consumer %

ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.

Create similar fields for the other segments

Corporate %

ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Home Office %

ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Then in the Waffle_Template datasource, create the following field

Colour : Consumer

[Orders (Sample – Superstore)].[Consumer %]>=SUM([Percent])

The above calculation will return true when the % Sales for Consumer is greater than or equal to the Percent value.

Repeat and create equivalent fields for the other segments

Colour : Corporate

[Orders (Sample – Superstore)].[Corporate %]>=SUM([Percent])

Colour : Home Office

[Orders (Sample – Superstore)].[Home Office %]>=SUM([Percent])

Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.

To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.

Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.

Name this sheet Consumer.

Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.

Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.

Building the KPIs

On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.

Create a new field

Percent of Total

ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)

format this to percent with 0 dp and add this to the Text shelf.

Create a new field

Segment UPPER

UPPER([Segment])

and add this to the Text shelf.

Format the text and align centrally

Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.

Creating the dashboard

Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.

Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.

My published viz is here.

Happy vizzin’!

Donna

Advertisement

Let’s show quarterly sales with end-user flexibility

It was Sean’s final #WOW2022 challenge of the year, and he set this task to provide alternative options for visualising time series across dimensions with high cardinality.

I had a play with Sean’s viz before I started tackling the challenge and noticed the following behaviour in addition to the requirements listed, which may or may not have been intentional.

When the cardinality of the dimension to display was more than 1 higher than the top n parameter, and the Show Others? option was set to Group By, the sales value to display was based on average sales rather than sum (as indicated in the requirements), and all the values not in the top n, were grouped under an ‘Other (Avg)’ label. But if the cardinality of the dimension was only 1 more than the top n parameter (so there was essentially only 1 value within ‘other’), then this value would display as itself (ie not labelled ‘other’) and the sales values would be summed rather than averaged (eg if the lines were to be split based on Ship Mode, and the top n was set to top 3 and Show Others? set to Group By, all four ship modes would display with the sum of sales rather than average).

This observation meant some of the calculations were slightly more complex than what I thought they would need to be initially, as I had to build in logic based on the number of values within a dimension.

So with that understood, let’s build the calcs…

Defining the calculations

Firstly we need some parameters

pDimensionToDisplay

String parameter defaulted to Subcategory, with the list of possible dimensions to split the chart by

pTop

integer parameter defaulted to 5

pShowOthers

I used an integer parameter with values 0 and 1 which I ‘aliased’ to the relevant values, defaulted to ‘Group Others’

I then needed to determine which dimension to be used based on the selected from the pDimensionToDisplay parameter.

DimensionSelected

CASE [pDimensionToDisplay]
WHEN ‘Subcategory’ THEN [Sub-Category]
WHEN ‘State’ THEN [State/Province]
WHEN ‘Ship Mode’ THEN [Ship Mode]
WHEN ‘Segment’ THEN [Segment]
ELSE ‘All’
END

From this, I could then use a Set to determine which of the values would be in the top n. Create a set of off DimensionSelected (right click -> Create -> Set)

Dimension Selected Set

select the Top tab, and create set based on the pTop parameter of Sales

To check this is working as expected, on a sheet, add DimensionSelected and Dimension Selected Set to Rows, add Sales to Text and sort by Sales descending. Show the pTop and pDimensionToDisplay parameters. Change the parameters and observe the results behave as expected.

Now we need to determine how many values are not in the set, ie, how many of the DimensionSelected values display as ‘Out’ in the above image.

Count Non-Set Items

{FIXED:COUNTD(IF NOT([Dimension Selected Set]) THEN [DimensionSelected] END)}

If the entry is not in the set, then return the entry, and the count the number of distinct entries we have. Using the FIXED level of detail calculation, wraps the value across every row.

Now this is understood, we need to work out whether we want to group the ‘non-set’ values under ‘other’

Dimension To Display

IF [pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1 THEN ‘Other (Avg)’
ELSE [DimensionSelected]
END

If we’re opting to ‘group’ the values, and the entry isn’t in the set, and we’ve got more than 1 entry that isn’t in the set, then we can label as ‘Other (Avg)’, otherwise, we just want the dimension value.

We use similar logic to determine whether to display the SUM or AVG Sales.

Sales to Display

IF [pShowOthers]=0 AND SUM([Count Non-Set Items]) > 1 THEN AVG([Sales])
ELSE SUM([Sales]) END

Format this to $ with 0 dp.

We can then remove the DimensionSelected from our view, and test the behaviour, switching the pShowOthers parameter

Building the core viz

On a new sheet, add Order Date set to continuous quarters (green pill) to Columns and Sales To Display to Rows and Dimension To Display to Label. Show all the parameters.

Create a new field

Colour

[pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1

add add to Colour shelf, and set colours accordingly.

Add Sub-Category, Ship Mode, State and Segment to the Filter shelf. Show them all, and then test the behaviour is as expected.

Extending the date axis

The date axis in the solution goes beyond 2022 Q4, and means the labels have a bit more ‘breathing space’. To extend the axis, click on Order Date pill in the Columns, and select Extend Date Range -> 6 months.

Then double click into the space next to the Sales To Display pill on the Rows and type MIN(0). This will create a second axis, and the date axis should now display to 2023 Q2

On the MIN(0) marks card, remove the Colour and Dimension To Display pills. Reduce the Size to as small as possible, and set the Colour opacity to 0%. Set the chart to dual axis, and synchronise the axis.

Finally tidy up the chart – hide the right hand axis, remove the title from the date axis, change the title on the left axis. Remove all row and column divider lines, but ensure the axis rulers are displayed. Title the viz referencing the pDimensionToDisplay parameter.

Building the dashboard

When putting the dashboard together, you need to ensure that the filters, parameters and main viz are all contained within a vertical container to ensure the viz ‘fills up’ the space when the controls section is collapsed.

The controls section itself is also a vertical container, which consists of 2 horizontal containers, one of which contains all the filters, and one which contains the parameters.

The layout tab shows how I managed this (I also like to rename the objects to keep better control).

The green Base vertical container is the first container. This essentially contains 3 ‘rows’ – the title, the User Controls vertical container and the viz itself.

The User Controls vertical container, then contains 2 rows itself – the Filters horizontal container (which has a pale grey background colour) and the Fine Tune horizontal container (which has a slightly darker grey background colour).

The User Controls vertical container is then set to ‘hide/show’ by selecting the whole container (click on the object in the item hierarchy on the left), and then selecting Add Show/Hide Button from the context menu. Adjust the button settings as required (I just altered the text displayed on hover) and then position accordingly.

It can take a bit of trial and error to get this right, and to get your containers working as expected. My published viz is here.

Happy vizzin’!

Donna

Interactive Proportional Brushing

For her final challenge of #WOW2022, Erica set this interesting challenge; to show how the sales of the top n Products within each Sub-Category compared against the total sales. An added twist was to flip the display between actual sales, and % of total.

It did take me a short while to get my starting point. When I’ve worked with the concept of proportional brushing before, it’s typically involved set actions and one viz driving the interactivity on another viz via a dashboard action. I didn’t have this. I’ve obviously come up with a solution, but I’m not sure if it’s overly complicated… it doesn’t seem it, but for some reason I feel it could have been simpler than I’ve made it. Only time will tell as I check out other solutions.

My solution involves table calcs, so as I do with many challenges, I’ll start by building out all the data I need in tabular form.

Defining the calculated fields

Start off by adding Sub-Category to Rows, Product Name to Rows and Sales into Text and sort descending. We want to identify the Product Names in the top n, so lets first rank the products per sub-category. Create a calculated field

Sales Rank

RANK_UNIQUE(SUM([Sales]))

Make it a discrete field, then add it to Rows between Sub-Category and Product Name. Set the table calculation to compute using Product Name.

The rows should be numbered from 1 upwards, and restart at 1 when the Sub-Category changes

We want to get the sum of the sales for all those products in the top n. We’ll use a parameter to identify the n

pTop

Integer parameter defaulted to 10

Then we’ll use another table calculation to get the sum of the sales in that top n

Sales in Top n

WINDOW_SUM(IF [Sales Rank]<=[pTop] THEN SUM([Sales]) END)

If the Sales Rank is less than or equal to the top parameter, then get the Sales for that row, then sum all the sales values up together.

Show the parameter on screen, and add Sales in Top n into the table, setting the table calc to once again compute using by Product Name.

You should see that the sum of the sales for the rows marked 1-10 should equate to the value in the Sales in Top n column for the Sub-Category

We also need to get the total sales for each Sub-Category, which we can do with

Sub Cat Sales

{FIXED [Sub-Category]: SUM([Sales])}

Format to $ with 0 dp and displayed in k.

With this , we can also determine the proportion of sales

Top n % of Total

[Sales in Top n]/SUM([Sub Cat Sales])

Format this to a % with 0 dp.

Add both these fields to the table, making sure the table calcs are set to compute using Product Name.

So now we’ve got the base fields that form the building blocks for the viz. But we have this added functionality where we want to show either actual values (ie Sales in Top n and Sub Cat Sales), or we want to show the Top n % of Total compared to the ‘whole’ ie 1.

To manage this, we first need the parameter to control the decision

pView

integer list parameter set to 0 or 1, with appropriate display values. and defaulted to Sales Value

We can then build

Measure 1 to Display

CASE [pView]
WHEN 0 THEN [Sub Cat Sales]
ELSE 1
END

and

Measure 2 to Display

CASE [pView]
WHEN 0 THEN [Sales in Top n]
ELSE [Top n % of Total]
END

Pop these into the table and show the pView parameter. Test changing the parameter and see the results. Don’t worry that the % shows 0.. you can format the Measure 2 to Display field to show 2 dp if you want to see the data really changing.

Building the bar chart

On a new sheet, add Sub-Category to Rows, Product Name to Detail and Measure 1 to Display to Columns.

Drag the Measure 2 to Display field onto the Measue 1 to Display axis, and release when the 2 green columns symbol appears

Set the Measure 2 to Display field to compute using Product Name, then move Measure Names from Rows to Colour, and adjust the colours. Ensure Measure 2 to Display is listed first in the colour legend, so the marks are on top. Set stack marks to off (Analysis menu -> Stack marks -> off) to make the bars a single block, rather than being split per Product.

Switch the view parameter to change the display and verify it behaves as expected

Add the Top n % of Total field to Rows and change to be discrete (blue). Ensure the field is set to compute by Product Name still.

Create a new field

Label to Display

CASE [pView]
WHEN 0 THEN [Sub Cat Sales]
ELSE NULL
END

Format this to $ with 0 dp and displayed in k. Add this to the Label shelf

Add Sub Cat Sales to the Tooltip shelf and then adjust the tooltip to match.

The final step (other than formatting) is to apply the sorting. Create a new field

Sort

CASE [pView]
WHEN 0 THEN SUM([Sub Cat Sales]) * -1

ELSE [Top n % of Total] *-1
END

Make this field discrete and then add to Rows in front of the Sub-Category pill. You should now find the bars are being sorted descended depending on the view being displayed

Finally, tidy everything up – Uncheck show header on both the Sort and Measure Values pills. Hide field labels for rows to remove the column headings. Change the font size/format of the first two columns, and adjust the colour of the bar label. Remove all gridlines, column banding etc.

Finally add to a dashboard. I used a horizontal container, text boxes and padding to build up the parameter selections at the top. My published viz is here.

Happy vizzin’!

Donna

Can you make a pie chart?

In his final challenge for 2022, Luke set this challenge asking us to recreate this pie chart. Although not mentioned in the challenge text, there was a hint on the splash page that map layers would be required.

I’ve only really used map layers in other #WOW challenges, and they actually involve maps. This challenge was obviously a bit different – utilising a functionality built for one purpose in an entirely different way. I remembered when map layers were first released there was a big buzz about the potential possibilities, and had seen some examples, but I’d never gotten round to trying out for myself, so this was the perfect opportunity (and one of the many plus points as to why I love doing #WOW challenges).

So where to start… good question. If you read up on the official Tableau KLs relating to map layers, it’s all about geography, and while the data source does have geographic data (State, City etc), they aren’t relevant in this case. In my ‘googling’ I found the following resources of use

The first 2 blogs helped me understand the need for the use of the MAKEPOINT function, Sam Parson’s Pies & Doughnuts viz helped me understand the calculation I’d need for the MAKEPOINT function, and the final blog post really helped with putting in all together.

Feel free to ignore the rest of this blog and use the above to help you out πŸ™‚

Building the first map layer

The first step is to create the geometry field we need to base this off of.

Zero

MAKEPOINT(0,0)

Double click this field, and it will automatically add the point centrally onto a map with Longitude and Latitude fields automatically generated too.

This is a key step in getting things started and enabling the use of map layers which we’re going to utilise.

Add Segment to Colour and adjust the colours. Change the mark type to pie chart and add Sales to angle. Increase the size to be as large as possible.

However the size isn’t as big as we need. To increase further use Ctrl-Shift-B (windows) or Cmd-Shift-B (mac) to increase the size further (Ctrl-B / Cmd-B) to reduce. This trick I found in the Interworks blog above. All Tableau key shortcuts are listed here.

Add Segment to the Label shelf. This completes our lowest map layer.

Building the second map layer

Drag Zero onto the map canvas, and drop it over the Add a Marks layer section that displays. This will add a second marks card called Zero(2).

On the marks card that is named Zero, rename it to Outer Pie.

On the marks card named Zero (2) rename to White Circle. Change the mark type to Circle, change the Colour to white and increase the size to leave a narrow border of the coloured pie underneath.

Building the third map layer

Drag another instance of Zero onto the canvas and add another marks layer. Rename Zero (3) to Inner Pie. Change the mark type to Pie chart and add Segment to Colour and Sales to angle. Increase the Size so it’s just smaller than the white circle. Change the opacity of the colour to 70% and add a white border (Colour shelf).

Adding the labels in the pie chart

The simplest way to do this is just to label the inner pie chart with the required fields and then manually move the labels from outside the pie to the desired location. However if your data changed in some way, eg the proportion of the slices changed, the labels may not be where you wanted without further tweaks.

So instead I’ve added a 4th map layer.

Add Zero once again to the sheet and add a marks layer. Rename this marks card to Labels -Inner Pie. Change mark type to Pie chart and add Segment to Detail, Sales to Angle and Sales to Label. Create a new calculated field

Pct

SUM([Sales]) / TOTAL(SUM([Sales]))

Format to % with 0 dp and and add to Label. Adjust the fonts of the labels so the Sales value is larger.

Increase the size of the pie chart so the labels are positioned ‘nicely’ within the segments of the Inner pie

Reduce the opacity of the ‘label’ pie chart to 0% and set the mark layer to be disabled

Finishing up

Adjust the tooltips to display as required (you’ll need to add Pct to the Tooltip shelf on both the Outer and Inner Pie mark cards).

Then remove the map background via the Map menu -> Background Maps -> None. Hide all axis and remove all gridlines/zero lines/row/columns dividers. You should now be left with a ‘clean’ pie chart which can be added to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Can you do this in one dashboard?

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

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

Building the KPI blocks

I started by creating new measures

Count Customers

CountD([Customer Name])

Count Orders

COUNTD([Order ID])

Count Cities

COUNTD([City])

Count Products

COUNTD([Product Name])

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

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

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

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

Building the bar chart

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

pDimension

String parameter which is hardcoded initially to the word Customers.

Create a new field which will determine which dimension to show

Dimension to Display

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

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

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

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

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

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

Building the dashboard

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

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

Select KPI

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

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

Remove the titles from displaying from the KPI block sheets.

Hiding and showing the relevant sheets

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

Crate a new calculated field

Dimension Selected

[pDimension]<>””

Similarly, create a new calculated field

Dimension Not Selected

[pDimension]=””

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

Repeat this against the other 3 KPI block sheets.

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

And that should be it. My published viz is here

Happy vizzin’!

Donna

Can you label & sort small multiples?

A colourful #WOW2022 challenge this week set by Kyle Yetter and using his favourite data – Baseball. Let’s jump straight in.

Building the required calculations

First up we need to calculate the core measure the viz is based on – % of wins

Win %

SUM([Wins])/SUM([Games])

I formatted this to 3 decimal places, then applied a custom number format to remove the leading 0 (custom number format looks like ,##.000;-#,##.000).

We also need to know the number of losses as this is part of the tooltip.

Losses

SUM([Games]) – SUM([Wins])

Let’s pop all these out into a table (I formatted all the whole numbers to display without any decimal places).

The viz however isn’t plotting the actual Win%, it’s plotting the difference from 50% (or 0.5), so values less than 50% are negative and those above are positive.

Plot Postion

[Win %] – 0.5

And we also need to know whether the Win% is above 50% or not

Above 50%

[Win %]>0.5

Pop these out onto the table too

The viz also displays the overall Win% for each team, and also uses this to sort the data. As it is used for sorting, we need to use an LoD calculation (rather than a table calculation).

Overall Win% LOD

{FIXED [Team]:SUM([Wins])} / {FIXED [Team]: SUM([Games])}

for each team, get the total wins, and divide by the total games for the team. Format this to 3 dp with no leading 0 as before.

pop this into the view (you’ll see it’s the same value for each row for a single team), and then apply a Sort on the Team field to sort descending by the Overeall Win% LOD.

Now we have the data sorted, we can create the fields needed to build the trellis chart.

I have already blogged challenges relating to trellis charts / small multiples (see here) which in turn reference other blogs in the community, so I’m not going to go into all the details. We just need to build two calculated fields to identify which row and which column each Team will sit in. The table is fixed at 6 columns wide as the data wea re using is static. Some solutions work with a more dynamic layout depending on how many entities you need to display. We’re keeping things simpler.

Cols

FLOAT(INT((INDEX()-1)%6))

Rows

FLOAT(INT((INDEX()-1)/6))

Add both these fields to the table as discrete dimensions (blue pills), and as they are both table calculations, set them both to Compute Using – > Team.

Building the Core Viz

On a new sheet, add Cols to Columns as discrete dimension, Rows to Rows as discrete dimension and Team to Detail. Set both Rows and Cols to Compute Using Team.

Add Year as continuous (green) pill to Columns and Plot Position to Rows and change the mark type to Bar and reduce the size. Sort the Team field based on Overall Win% LOD descending.

Add Wins, Losses, and Win% to the Tooltip shelf and adjust the tooltip to display as required. Add Above 50% to the Colour shelf (you may need to readjust the size). Leave the colours as they are for now – we’ll deal with this later.

Adding the labels

Create a new calculated field

Dummy Plot

FLOAT(IF [Year]=2000 OR Year = 2020 THEN 0.35 END)

This is basically going to position a mark at height 0.35 but only if the year is either 2000 or 2020. These values were all just based on a bit of trial and error as to what worked to get the desired result.

Also create a field

LABEL:Team

IF [Year]=2000 THEN [Team] END

and

LABEL:Win%

IF [Year]=2020 THEN [Overall Win % LOD] END

format this to 3dp and exclude the leading 0.

Add Dummy Plot onto Rows and change the mark type of this measure to circle. Amend the Tooltip of this marks card so it’s empty.

Add LABEL:TEAM and LABEL:Win% to the Label shelf, and adjust the label so both fields sit side by side (only 1 value will only ever actually display). Adjust the table calculation of both the Rows and Cols pills so they now compute using both the Team and the LABEL:Team fields.

Adjust the alignment of the labels so they are positioned bottom centre. Set the font colour to match mark colour and bold.

Then reduce the size of the circle mark to as small as possible, reduce the opacity of the mark colour to 0.

Now make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card.

Hide all the headers and axis (uncheck Show Header), remove all grid lines, zero line, axis rulers.

Hide the null indicator (bottom right).

Colouring by Team

Copy the colour palette text Kyle provided into your preferences.tps file (usually located in the My Tableau Repository directory). For more information on working with custom colour palettes see thisΒ Tableau help article.

You’ll need to save your workbook and re-open for the new palette to be available for use.

In order to prevent having to manually set all the colours (and believe me you don’t want to do this!), perform the following steps in order

  • Add Team to also be on the Colour shelf. Click on the 3 dots (…) that are to the left of the Team pill on the All marks card, and change it to Colour. This means there are now 2 fields on colour. Move the Team field so it is listed above the Above 50% pill. This means your colour legend should be listed as <Team>, <True|False>
  • Adjust the Sort of the Above 50% pill, so it is manually sorted to list True before False.

  • Now change the Sort on the Team field so it is sorted alphabetically ascending instead. This will cause the viz to change its sort order, but don’t worry for now. It also changes the list on the colour legend, so ARI, True is listed first then ARI, False etc.

  • Now edit the Colour Legend and select the new MLB Team Colours palette we added. Click the Assign Palette button to automatically assign the colours. As we’ve made sure the entries listed are in the right order, they should get the correct colours.

  • Change the Sort on the Team field back to be based on Overall Win% LOD descending

And that should be it. You can now add the viz to a dashboard and publish. My published version is here.

Happy vizzin’!

Donna

Let’s Build a Map!

Inspired by a viz from Klaus Schulte, Sean Miller set this week’s challenge to recreate a hex map with state shapes using Superstore.

Building the data model

I referenced this Tableau blog post and downloaded the HexmapPlots excel file included. I then used a relationship to ‘join’ the Sample – Superstore excel file I was using with the HexmapPlots file, joining on State/Province = State

Since I had the other blog post already open, I then followed the steps included to start building the map.

Building the hex map

Add Column to Columns and aggregate to AVG, and add Row to Rows and also aggregate to AVG. Add State/Province to Detail. Edit the Rows axis and set to be Reversed.

Note – it’s possible you may have extra States showing. As I’m writing I’ve realised I’m rebuilding against an extracted data source that has a filter I originally applied as a global filter, which has now been included in the extract. So you may need to add State/Province to the Filter shelf, and set to exclude NULL and District of Colombia. This filter will need to be applied to all sheets you build.

Change the mark type to Shape and select a Hex shape. I already have a palette full of Hex shapes, but the blog post provides a shape to use and add as a custom shape if you haven’t got one. Increase the size of the marks.

Create a new field

Profit Ratio

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

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

Now add a second instance of Row to the Rows shelf and set to AVG again. Set the mark type of this marks card to Map. Remove the Profit Ratio field from Colour on this card too. Assuming your Map location is set to USA, you should have State outlines depicted (Map -> Edit Location).

Set the chart to be dual axis and synchronise the axis. The State shape axis should now be inverted. Independently adjust the sizes of the Hex shape and the state shapes, so the states sit inside the hexagons.

Right click on the right axis and move marks to back. The adjust the Colour of the hex shapes so its around 85% transparent.

Now adjust the Tooltip on the hex marks to match the requirement.

To fill-out the available area, I also chose to fix both the axis (right click axis -> edit axis). The Column x-axis I set to range from 1 – 12, and the Row y-axis, I set to range from -0.9 – 9. Then hide all axis, and remove all row/column gridlines, divider lines, axis lines and zero lines. Hide the 10 unknown indicator, and set the background colour of the whole worksheet to a pale grey.

Building the Line Chart

This is super simple, Tableau 101 πŸ™‚

Add Order Date to Columns and set to be a continuous month (green pill showing month-year). Add Sales to Rows. Change the colour of the line to grey. Hide the x-axis and remove all gridlines, dividers, axis lines and zero lines. Set the background colour of the worksheet to None (ie transparent). Update the tooltip.

Building the Scatter Plot

Add Sales to Columns, and Profit to Rows. Add State/Province to Detail shelf and add Profit Ratio to Colour. Change the mark type to circle. Remove all gridlines, row column dividers and axis rulers. Only the zero lines should remain. Adjust the tooltip and set the worksheet background to None (transparent).

Putting it all together

Create a dashboard and set the size as stated. Set the background of the dashboard to pale grey (Dashboard – > Format).

Add the Hex map, and hide the title. Click on the Profit Ratio legend object and set to be floating. Then remove the right hand vertical container. Move the Profit Ratio legend to a suitable location.

Then add a text box as a floating object and use it to create the title. Add both the trend line and the scatter plot charts as floating objects without titles. Just position them as required. You can always use gridlines (Dashboard -> Show Grid) to help you line things up.

Finally add the interactivity.

Add a highlight dashboard action which highlights the hex map and the scatter plot when either of the other is selected ‘on hover’, and just targets the State/Province field.

Then add a Filter action which on hover of the Trend chart, targets the remaining charts.

And hopefully that’s it. My published viz is here.

Happy vizzin’!

Donna

Average Patient Wait Times

Erica set this challenge this week to provide the ability to compare the average wait time for patients in a specific hour on a specific day, against the ‘overall average’ for the same day of week, month and hour.

As with most challenges, I’m going to first work out what calculations I need in a tabular form, and then I’ll build the charts.

  • Building the Calculations
  • Building the Wait Time Chart
  • Building the Patient Count Chart

Building the Calculations

The Date field contains the datetime the patient entered the hospital. The first step is to create a field that stores the day only

Date Day

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

From this I could then create a parameter

Choose an Event Date

date parameter, defaulted to 6th April 2020, and custom formatted to display in dddd, d mmmm yyyy format. Values displayed were a list added from the Date Day field.

I also created several other fields based off of the Date field:

Month of Date

DATENAME(‘month’,[Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns April.

Day of Date

DATENAME(‘weekday’,[Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns Friday.

Hour of Date

DATEPART(‘hour’, [Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns 16. I added this to the Dimensions (top half) of the data pane.

The viz being displayed only cares about data related to the day of the week and the month of the date the user selects, so we can also create

Records to Keep

DATENAME(‘weekday’, [Choose an Event Date]) = [Day Of Date]
AND
DATENAME(‘month’, [Choose an Event Date]) = [Month of Date]

On a new sheet, add this to the Filter shelf and set to True. When Choose an Event Date is Monday, 06 April 2020, then Records To Keep will ensure only rows in the data set relating to Mondays in April will display. Let’s build this.

Add Hour of Date, Day of Date, Month of Date to Rows, and then also add Date as a discrete exact date (blue pill).

You can see that all the records are for Monday and April. And by grouping by Hour of Date, you can see how many ‘admissions’ were made during each hour time frame. Remove Day of Date and Month of Date – these are superfluous and I just added so we could verify the data is as expected.

The measures we need are patient count and patient wait time. I created

Count Patients

COUNTD([Patient Id])

(although the count of the dataset will yield the same results).

Add Count Patients and Patient Waittime into the table.

This is the information that’s going to help us get the overall average for each hour (the line chart data). But we also need to get a handle on the data associated to the date the user wants to compare against (the bar chart).

Count Patients Selected Date

COUNTD(IF [Choose an Event Date] = [Date Day] THEN [Patient Id] END)

Wait Time Selected Date

IF [Choose an Event Date] = [Date Day] THEN [Patient Waittime] END

Add these onto the table – you’ll need to scroll down a bit to see values in these columns

So what we’re aiming for, using the hour from 23:00-00:00 as an example: There are 3 admissions on Mondays in April during this hour, 2 of which happen to be on the date we’re interested in. So the overall average is the sum of the Patient Waittime of these 3 records, divided by 3 (650.5 / 3 = 216.83), and the average for the actual date (6th April) in that hour is the sum of the Wait Time Selected Date for the 2 records, divided by 2 (509.5 / 2 = 254.75).

If we remove Date from the table now, we can see the values we need, that I’ve referenced above.

So let’s now create the average fields we need

Avg Wait Time

SUM([Patient Waittime])/[Count Patients]

Avg Wait Time Selected Date

SUM([Wait Time Selected Date]) / [Count Patients Selected Date]

Pop these into the table

This gives is the key measures we need to plot, but we need some additional fields to display on the tooltips.

Avg Wait Time hh:mm

[Avg Wait Time]/24/60

This converts the value we have in minutes as a proportion of the number of minutes in a day. Apply a custom number format of h”h” mm”mins”

Do the same for

Avg Wait Time Selected Date hh:mm

[Avg Wait Time Selected Date]/24/60

Apply a custom number format of h”h” mm”mins”

Add these to the table.

For the tooltip we need to show the hour start/end.

TOOLTIP: Hour of Date From

[Hour of Date]

custom format this to 00.00

TOOLTIP: Hour of Date To

IF [Hour of Date] =23 THEN 0
ELSE [Hour of Date]+1
END

custom format this to 00.00.

We can now start building the charts.

Building the Wait Time Chart

On a new sheet, add Records To Keep = True to the Filter shelf. Add Hour of Date as continuous dimension (green pill) to Columns and Avg Wait Time Selected Date to Rows. Change the mark type to Bar.

Then add Avg Wait Time to Rows and change the mark type of this measure to Line. Make the chart dual axis, synchronise the axis and adjust the colours of the Measure Names legend.

Add TOOLTIP: Hour of Date From and TOOLTIP: Hour of Date To and Avg Wait Time Selected Date hh:mm to the Tooltip of the bar marks card, and adjust the tooltip accordingly.

Add TOOLTIP: Hour of Date From, TOOLTIP: Hour of Date To, Avg Wait Time hh:mm, Day of Date and Month of Date to the Tooltip of the line marks card, and adjust the tooltip accordingly.

Remove the right hand axis. Remove the title of the bottom axis. Change the title on the left hand axis. Hide the ‘9 nulls’ indicator (right click). Remove column gridlines, all zero lines, all row and column dividers. Keep axis rulers. Format the numbers on the x-axis.

Change the data type of the Hour Of Date field to be a decimal, then edit the x-axis and fix to display from -0.9 to 23.9.

Amend the title of the chart to match the title on the dashboard.

Building the Patient Count Chart

On a new sheet, add Records To Keep = True to Filter, Hour of Date as continuous dimension to Columns and Count Patients Selected Date to Rows. Change Mark Type to Bar.

Right click on the y-axis and edit the axis. Remove the title, and check the Reversed checkbox to invert the axis. Edit the x-axis and fix from -0.9 to 23.9 as before.

Add TOOLTIP: Hour of Date From and TOOLTIP: Hour of Date To to the Tooltip and adjust accordingly.

Set the colour of the bars to match the same blue of the bars in the wait time chart, then adjust the opacity to 50%.

Remove all gridlines and zero lines. Retain axis ruler for both row and columns. Retain row divider against the pane only. Hide the x-axis.

We need to display a label for ‘Number of Patients’ on this chart. We will position it based on the highest value being displayed (rather than hardcoding a constant value). For this we create a new calculated field

Ref Line

WINDOW_MAX([Count Patients Selected Date]) +1

This returns the maximum value of the Count Patients Selected Date field, adds 1 and then ‘spreads’ that value across all the ‘rows’ of data.

Add this field to the Detail shelf. Then right click on the y-axis and Add Reference Line.

Add the reference line based on the average of the Ref Line field, and label it Number of Patients. Don’t show the tooltip, or display any line.

Once added, then format the reference line, and adjust the size and position of the text.

The sheets can now be added to a dashboard, placing them above each other. They should both be set to Fit Entire View. The width of the y-axis on the Patient Count chart will need to be manually adjusted so it is in line with the Wait time chart, and ensures the Hour columns are aligned..

My published viz is here.

Happy vizzin’!

Donna

What is the lifetime value of customers?

Luke Stanke set the #WOW2022 challenge this week (see here) asking us to visualise the lifetime value of customers. I have to admit, I did struggle to really understand what was being requested, and to get the numbers to match Luke’s. I ended up referring to my own blog post on a similar challenge Ann Jackson set in week 2 of 2021 to get the calculations I needed πŸ™‚

We first need to define the quarter that each customer made their first purchase.

Customer First Order Quarter

DATE(DATETRUNC(‘quarter’, {FIXED [Customer ID]:MIN([Order Date])}))

The {FIXED LOD} calculation returns the minimum order date per customer, then truncates this to the first day of the quarter that date falls in.

We then need to determine the difference in quarters between the Customer First Order Quarter and the quarter associated to the Order Date of each order in the data set. The requirement indicated we needed to add 1 to the result. I split this into multiple calculated fields. Firstly,

Order Date Quarter

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

gets me the quarter of each Order Date, then

Quarters Since First Purchase

DATEDIFF(‘quarter’, [Customer First Order Quarter], [Order Date Quarter])+1

Let’s pop these fields out into a table to check things are behaving as expected.

Add Customer First Order Quarter and Order Date Quarter to Rows as discrete exact dates (blue pills), and add Quarters Since First Purchase to the Text field, but set it to be a dimension, so it is disaggregated.

Now we need to count the number of distinct customers that made a purchase in each Customer First Order Quarter (the cohort)

Count Customers Per Cohort

{FIXED [Customer First Order Quarter]: COUNTD([Customer ID])}

Add this to to the sheet, along with Sales (I moved Quarters Since First Purchase to rows too)

As expected, we can see the same customer count for each Customer First Order Quarter cohort.

We’ve now got all the building blocks to move on the the next requirement, but I’m going to rearrange the table a bit, to start to reflect the data actually needed for the output.

The x-axis of the chart is going to be based on the Quarters Since First Purchase field, so move that to be the first column of data (1st entry in Rows). Then remove Customer First Order Quarter and Order Date Quarter, as we don’t need this level of information in the final viz.

We now have the sum of all the customers and the total sales, so we can now create the division reqiurement

Sales / Customer

SUM([Sales])/SUM([Count Customers Per Cohort])

I set this to currency $ with 0 dp.

Add this to the table

Then to make this a running total, create a Running Total quick table calculation off of this field (right click on field -> Quick Table Calculation -> Running Total). Add back in Sales/ Customer.

We’ve now got all the components needed to build the viz, but do require an additional calculation to be displayed in the tooltip, which is the difference between each row.

Right click the existing running total Sales / Customer pill (the one with the triangle) and choose to Edit Table Calculation. Tick the Add secondary calculation checkbox and choose the Difference From table calc to run down the table, making the calc relative to the previous row.

Re-add a Running Total quick table calc to the other Sales / Customer pill, and then add Sales / Customer back into the view (it’s annoying that Tableau won’t let you add multiple pills of the same measure name unless they have a different calculation against them).

The snag with this is that we need a value to display for the first row. We need to create a new field that can reference the data in the second table calculation. Click and drag the ‘difference’ table calculation field into the Data pane, and name the field Difference. It should look like below, but you shouldn’t have needed to type any of that.

Difference

ZN(RUNNING_SUM([Sales / Customers])) – LOOKUP(ZN(RUNNING_SUM([Sales / Customers])), -1)

Now create a new calculated field

Tooltip:Difference

IF FIRST()=0 THEN [Sales / Customers] ELSE [Difference] END

Set this to a customer number format of 1dp, prefixed by + (the data is always cumulative so is never going to have a -ve value, so this works).

Now we can build the viz.

On a new sheet add Quarters Since First Purchase to Columns as a continuous dimension (green pill), then add Sales / Customers to Rows and set to be a Running Total quick table calc. Change the mark type to be a Gantt Bar.

Create a new field

Size

[Tooltip:Difference]*-1

and add this to the Size shelf.

Add a second instance of the Sales / Customer running total calc (press ctrl and click and drag the existing pill in rows to create another next to it). Change the mark type of this to be bar. Remove the Size pill, and then click the Size shelf button, and set the Size to be fixed, aligned left.

Now make the charts dual axis and synchronise the axis. Set the colour of each mark type to the relevant palette, and set the mark borders to None. Hide the right hand axis.

On the All marks card, add the Tooltip:Difference and Count Customers Per Cohort fields to the Tooltip shelf, and amend the tooltip to match.

On the bar marks card, click the Label shelf button and tick the show mark labels checkbox. Align these left.

Remove the left hand axis, remove all gridlines and row/column dividers. Add column axis ruler and dark tick marks

Edit the x-axis and rename the title to Quarter of Order.

If you find you have the x-axis going from 0 to 18, then change the datatype of Quarters Since First Purchase from a whole number to decimal. (right click pill in data pane -> change data type -> Number(decimal).

Add the chart to a dashboard and boom! you’re done. My public viz is here. (note, I did notice some vertical dividers displaying in the area chart on public, but think this is a Tableau Public ‘bug’ as I’ve switched off all the lines I can think of, and Desktop displays fine….

Happy vizzin’!

Donna

Selected Sub-Category Influence

Ann Jackson made a special guest appearance this week, setting this challenge to introduce the newly released 2022.3 feature of dynamic zone visibility. As a consequence, a pre-requisite to completing this challenge is to install v2022.3 πŸ™‚

I used 6 sheets to build my solution, and I’ll step through each one, and then what’s required to put it all together.

  • Building the Sub-Category Picker
  • Building the Sub-Category Counter
  • Building the Bar Chart
  • Building the Line Chart
  • Building the Viz in Tooltip
  • Building the Dot Plot
  • Hiding & Showing the Charts
  • Adding & Removing Sub-Categories

Building the Sub-Category Picker

On a new sheet, add Sub-Category to Rows and change the mark type to circle. Right click on the Sub-Category field in the Data pane, and Create -> Set. Select the entries from Accessories down to Copiers. This will create a new field in the data pane called Sub-Category Set. Add this field to the Colour shelf, and adjust colours according to whether the values are In or Out of the set.

Add a grey border around the circles (via the Colour shelf) and increase the size a bit. Format the text of the sub-categories so its larger and right aligned. Remove all row/column dividers and hide field labels for rows to remove the Sub-Category column title (right-click on the column title). Adjust the Tooltip. Add a title to the sheet and then name the sheet Sub Cat Picker or similar.

Building the Sub-Category Counter

Create a new calculated field

Count Sub Cats Selected

COUNTD(IF [Sub-Category Set] THEN [Sub-Category] END)

If the Sub-Category is in the set, the return the Sub-Category and count the number of distinct entries.

Then create

Count Total Sub Cats

COUNTD([Sub-Category])

This just counts them all.

On a new sheet, add both fields to the Text shelf, and adjust the text accordingly.

Remove the tooltip so it doesn’t display. Name the sheet Set Count Label or similar.

Building the Bar Chart

Create a new field

Profit Ratio

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

and format to a % with 0 dp.

On a new sheet, add Profit Ratio to Rows and Sub-Category Set to Columns and also to Colour.

Right click on the text ‘In’ either on the colour legend or at the bottom of the bar, and Edit Alias. Change the text to SELECTED. Do the same thing for the text ‘Out’ and change to OTHER.

Add a row grand total (Analysis menu -> Totals -> Show Row Grand Totals). Adjust the colour of the grand total bar. Right click on the text ‘Grand Total’ and select Format. In the pane on the left hand side, change the Grand Total Label to read ALL PRODUCTS.

Create a calculated field

Overall PR

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

The { } make this a FIXED Level of Detail (LoD) calculation, so calculates over the complete data set.

Then create

PR Difference from Overall

[Profit Ratio]-SUM([Overall PR])

and format this to a custom number format of +0.0%;-0.0%;

Adding the second semi-colon implies there is a format for +ve numbers, -ve numbers and zero. In this instance we want zero difference to be displayed as blank.

Add both these fields the the Label shelf and adjust the font/layout accordingly, and match mark colour. Adjust the tooltip too.

Remove the profit ratio axis, remove all gridlines and row/column dividers. Add an axis ruler to the columns. Adjust the colour/size of the column labels. Hide the In/Out of Sub-Category Set column label (hide field label for columns). Add a title to the sheet and name the sheet Bar Chart or similar.

Building the Line Chart

On a new sheet, and Order Date to Columns and set to be a continuous (green) pill at the Quarter-Year level. Add Profit Ratio to Rows and Sub-Category Set to Colour.

Create a new calculated field

PR Per Quarter

{FIXED DATETRUNC(‘quarter’, [Order Date]):SUM([Profit])} /
{FIXED DATETRUNC(‘quarter’, [Order Date]):SUM([Sales])}

and format this to % with 0dp.

Add this to Rows next to Profit Ratio.

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card, and remove the In/Out Sub-Category Set pill from the colour shelf of the PR Per Quarter marks card. Manually adjust the colour of this line to the appropriate shade.

On the All marks card, click the Label shelf, and check the Show mark labels option and select line ends. Adjust the font of the labels to be smaller, bold and to match mark colour.

Right click on the PR Per Quarter axis on the right hand side and select move marks to back, the right click again and uncheck Show Header to hide that axis.

Right click on the bottom axis, and Edit Axis and remove the axis title. Edit the left hand axis and amend the title so its capitalised.

Format the font of both axis, so the text is smaller, and then remove all row/column dividers, and all gridlines and zero lines. Add axis rulers for both the rows and columns.

Then add a sheet title and subtitle and name the sheet Line Chart or similar. I use this site to get the circular symbols used in the subtitle.

Building the Viz in Tooltip

The line chart shows another chart on hover.

On a new sheet, add Order Date as a blue discrete pill set to the Quarter-Year level, to Rows then add Sub-Category Set to Rows too. On the Columns shelf, double click and manually type in MIN(1). Add Sub-Category Set to Colour, then edit the MIN(1) axis to fix it from 0 to 1.

Add subtotals (Analysis menu -> Totals -> Add all subtotals). This will add a Total row to each section. Manually adjust the colour of the Total bar if need be via the colour legend.

Right click on the text ‘Total’ in the chart, and format. Amend the Total label to read ‘ALL PRODUCTS’ instead.

Add Profit Ratio to the Label and ensure the font matches mark colour. You may need to adjust the font size and boldness, and expand the row height a bit to see the text.

Hide the Order Date column, adjust the font of the Sub-Category Set column to be darker/bolder and right aligned, and adjust the column width so all the text is displayed.

Hide the MIN(1) axis, remove all row/column dividers and hide the Sub-Category Set column label. Then set the sheet to Entire View, and name the sheet VIT or similar.

Return the Line Chart sheet, and on the Tooltip shelf of the All marks card, adjust the tooltip to display the Order Date and insert a reference to the VIT sheet via the Insert -> Sheets -> VIT option

Adjust the height and width to suit.

Building the Dot Plot

On a new sheet, add Sub-Category Set to Rows and Profit Ratio to Columns. Change the mark type to circle. Then add Product ID to the Detail shelf and Sub-Category Set to Colour. Add column grand totals and adjust the colour of the grand total if need be. Format the ‘Grand Total’ text so it reads ALL PRODUCTS.

To get a clearer idea of how many products there are, we are going to randomly spread the dots across a vertical y-axis. For this we create

Jitter

RANDOM()

This just returns a number between 0 and 1.

Add this field to Rows and change it to be a Dimension. Adjust the opacity of the Colour to 50%.

Hide the Jitter axis. Make the header column wider, so the text doesn’t wrap, and adjust the text to b bigger and bolder and right aligned. Hide the Sub-Category Set column heading. Adjust the size and title of the Profit Ratio axis. Remove all gridlines and column dividers.

Right click on the Profit Ratio axis and add a reference line, which is set per pane to the the Total of the Profit Ratio. Use the Value as label and set the line to be a dotted black line at 100% opacity.

Add Product Name to the Tooltip and adjust accordingly. Add a title and name the sheet Dot Plot or similar.

Hiding & Showing the Charts

We’re going to control which sheet displays by use of a parameter, so I created

pChartSelector

an integer list from 1-3 which are mapped to the 3 display values

Then create a dashboard sheet and using layout containers build out the dashboard. I used a horizontal container in the centre of my dashboard. Within that I used a vertical container to house the Sub-Category Picker and the Sub-Category Counter. Then the 3 charts (bar, line and dot plot) were arranged next to that. I fixed the width of the vertical container with the picker and counter. The pChartSelector parameter is then added at the top right. I made use of both inner and outer padding and background colours of pale grey and white to get the look as reqiured.

To make the hide/show functionality, I created the following fields

Show bar

[pChartSelector]=1

Show line

[pChartSelector]=2

Show dot plot

[pChartSelector]=3

I added Show bar to the Detail shelf of the bar chart sheet, Show line to the Detail shelf of the line chart sheet and Show dot plot to the Detail shelf of the dot plot sheet.

Then back on the dashboard, I selected the bar chart sheet (so it’s surrounded by a dark grey border), and on the Layout tab on the left hand side, I checked the Control visibility using value checkbox and selected the Show bar field

I then repeated this process, this time selecting the line chart sheet, and when I checked the Control visibility checkbox, I selected the show line field instead. this made the line chart disappear, since my parameter was set to ‘Compared to the Total’ which was equivalent to the parameter = 1 and not 2. Changing the parameter to ‘Over Time’ and my line chart showed and the bar disappeared.

Repeat the process again for the dot plot, selecting the show dot plot field instead. Now only 1 chart should display at a time.

Adding & Removing Sub-Categories

The final step is to add the interactivity to allow selection and removal of a sub-category when clicking on the circles of the Sub-Category Picker sheet.

First, you need to add a dashboard action which changes set values

Add Sub-Categories

Uses the Sub Cat Picker sheet as source and on Select targets the Sub-Category Set by Adding values to the set. The values are retained when the selection is cleared.

Then add another dashboard action to change set values. This one is called

REMOVE

Uses the Sub Cat Picker sheet as source and via the Menu targets the Sub-Category Set by Removing values from the set. The values are retained when the selection is cleared.

The title of REMOVE is what is then displayed in the text of the tooltip when a circle that has been added is the clicked again.

Phew!

Quite a lengthy post this week, but there’s a lot going on. My published viz is here.

Happy vizzin’!

Donna