Tag: parameter

Can you create this ‘zoom and reset’ scatter plot?

Donna ColesLeave a comment

For this week’s #WOW challenge we’re focusing on parameter driven axes ranges.

Building the basic viz

After connecting to the dataset, add Sales to Columns and Profit to Rows. Add Customer Name to Detail and change the mark type to circle.

The chart is divided by reference lines which I chose to define as parameters (but these were additional to the ones mentioned in the challenge requirements).

pProfitRef

integer parameter defaulted to 0

pSalesRef

integer parameter defaulted to 5000

Add both parameters to the Detail shelf, the add a reference line on the Sales axis that refers to the pSalesRef value.

and then repeat and add a reference line to the Profit axis referencing the pProfitRef field.

To colour the 4 segments of the chart, create new fields

Sales per Customer

{FIXED [Customer Name]:SUM([Sales])}

and

Profit per Customer

{FIXED [Customer Name]: SUM([Profit])}

These capture the total sales / profit at the customer level, and we can then determine which quadrant each customer is in by

Cohort

IF [Sales per Customer]>=[pSalesRef] THEN
IF [Profit Per Customer]>=[pProfitRef] THEN ‘High Sales, High Profit’
ELSE ‘High Sales, Low Profit’
END
ELSE
IF [Profit Per Customer]>=[pProfitRef] THEN ‘Low Sales, High Profit’
ELSE ‘Low Sales, Low Profit’
END
END

Add this to the Colour shelf and adjust colours to suit. Then set the opacity to 50% and increase the size of the marks a bit.

To add matching coloured borders around the marks, add another instance of Profit to Rows. Change the mark type of the 2nd Profit marks card to Shape and change the shape to be an open circle. Set the opacity to 100%. Set the chart to dual axis and synchronise the axis.

Adjust the Tooltip and hide the right hand axis (uncheck show header).

Dynamically adjust the axis

The axes will change by adjusting them to refer to parameters. By default we need the axis to try to replicate what it gets set to automatically. For this we need to capture the maximum and minimum sales and profit values and add a ‘buffer’ to give the extra space. I played around with a few options for the buffer, so created a parameter to store this until I got the value that seemed to work best (again this was an additional parameter that I added to just help not having to change multiple calculated fields as I got the value I wanted.)

pBuffer

integer parameter defaulted to 2000

Then create 4 fields to define the max & min of the two measures +/- buffer

Min Sales + Buffer

{MIN([Sales per Customer]) – [pBuffer]}

Max Sales + Buffer

{MAX([Sales per Customer]) + [pBuffer]}

Min Profit + Buffer

{MIN([Profit Per Customer]) – [pBuffer]}

Max Profit + Buffer

{MAX([Profit Per Customer]) – [pBuffer]}

Then create 4 parameters which will define the values we ca use to set the axis

pX-Min

float parameter that is set to the Min Sales + Buffer field when workbook opens (selecting this will then populate the value)

Create further parameters

pX-Max

float parameter that is set to the Max Sales + Buffer field when workbook opens

pY-Min

float parameter that is set to the Min Profit + Buffer field when workbook opens

pY-Max

float parameter that is set to the Max Profit + Buffer field when workbook opens

Once all the parameters exist, edit the Sales Axis and change the axis to use a custom range that references the pX-Min and pX-Max parameters

Do the same for the Profit axis, but reference the pY-Min and pY-Max parameters instead.

Finally while we’re still on the workbook, create two new fields

True

TRUE

and

False

FALSE

and add these to the Detail shelf. We’ll need these later to stop marks highlighting when we click.

Name the sheet Scatter or similar.

Building the Reset button

This actually requires another sheet (even through the requirements says 1 sheet).

Create a new field

Reset Axis

‘Reset Axis’

Add this to the Text shelf of a new sheet. Change the mark type to shape and select a transparent shape (refer to this blog to understand how to create this).

Set the view to Entire View and align the font middle centre and increase the font size. Set the background of the whole worksheet to black. Adjust the tooltip. Add Min Sales + Buffer, Max Sales + Buffer, Min Profit + Buffer and Max Profit + Buffer to the Detail shelf, along with the True and False fields.

Name the sheet Reset or similar

Adding the interactivity

Add the Scatter and Reset sheets to a dashboard, removing any parameters/legends etc that get added. Create dashboard parameter actions to set the axis parameters when selections are made on the scatter plot:

Set X Max

On select of the Scatter sheet, set the pX-Max parameter, passing in the maximum value of the Sales field.

Set X Min

On select of the Scatter sheet, set the pX-Min parameter, passing in the minimum value of the Sales field.

Set Y Min

On select of the Scatter sheet, set the pY-Min parameter, passing in the minimum value of the Profit field.

Set Y Max

On select of the Scatter sheet, set the pY-Max parameter, passing in the maximum value of the Profit field.

Also create dashboard parameter actions to ‘reset’ the axis parameters when the Reset button is clicked:

Reset X Min

On select of the Reset sheet, set the pX-Min parameter, passing in the minimum value of the Min Sales + Buffer field.

Reset X Max

On select of the Reset sheet, set the pX-Max parameter, passing in the maximum value of the Max Sales + Buffer field.

Reset Y Min

On select of the Reset sheet, set the pY-Min parameter, passing in the minimum value of the Min Profit + Buffer field.

Reset Y Max

On select of the Reset sheet, set the pY-Max parameter, passing in the maximum value of the Max Profit + Buffer field.

All these 8 actions should now combine to drive the ‘zoom & reset’ functionality.

Finally, the last step to make the display ‘nicer’ is to deselect the marks from being highlighted when selected. Add a dashboard filter action

Deselect Scatter

on select of the scatter object on the dashboard, target the scatter sheet directly, passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat an create a similar action for Deselect Reset.

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

Happy vizzin’!

Donna

Can you build a colour-coded filter?

Donna ColesLeave a comment

Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.

Defining the parameters

We need 3 parameters for this challenge

pMeasure

strig parameter that lists the two options Profit and Sales; defaulted to Profit.

pProfitThreshold

integer parameter that lists the specified values, defaulted to 2,000

pSalesThreshold

integer parameter that lists the specified values, defaulted to 30,000

Building the core scatter plot

Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.

When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:

Ref – Profit Threshold

IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END

Ref – Sales Threshold

IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END

We also need to define the average per measure for each region:

Sales Avg Per Region

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

Profit Avg Per Region

{FIXED [Region]: SUM([Profit])} / {FIXED: COUNTD([Sub-Category])}

and then we can create

Ref – Profit Avg

IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END

Ref – Sales Avg

IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END

Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.

Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.

Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.

Colour

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0)
ELSE
IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0)
END

Set this to be discrete and then add to the Colour shelf and adjust accordingly.

Creating the colour-coded filter

The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.

We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)

Region Indicator

{FIXED [Region]: (

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’)
ELSE
IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’)
END
)}

I then created

Filter – Region

[Region Indicator] + ‘ ‘ + [Region]

Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.

Format the Tooltip

The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:

Tooltip – Measure Value

IIF([pMeasure]=’Profit’,[Profit],[Sales])

Tooltip -Threshold

IIF([pMeasure]=’Profit’, [pProfitThreshold], [pSalesThreshold])

Tooltip – Measure Value Above Avg

IF [Colour] = 1 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Measure Value Below Avg

IF [Colour] = 0 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Above text

IF [Colour] = 1 THEN ‘above’ END

Tooltip – Below text

IF [Colour] = 0 THEN ‘below’ END

Label – Region

IIF(COUNTD([Region])>1, ‘All regions’, MIN([Region]))

Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.

Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.

Building the Overall Indicator

For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.

On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.

We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.

Region Indicator – Is Below

IIF( [Region Indicator] = ‘🟡’,1,0)

Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.

Create

Overall Region Indicator

IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)

This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.

Create a field

Filter – Index = 1

INDEX() = 1

Add to Filter shelf and set to True

Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).

Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.

Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).

Finally create

Tooltip – Overall Indicator

IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)

Add this to Tooltip and then set the background colour to pale blue.

Building the dashboard & adding dynamic zone visibility

Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.

Then create 2 new boolean calculated fields

Is Profit

[pMeasure] = ‘Profit’

Is Sales

[pMeasure] = ‘Sales’

Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)

Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.

Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.

Happy vizzin!

Donna

Can you create this seemingly simple line chart?

Donna ColesLeave a comment

Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.

After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….

Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too 🙂

What didn’t work

The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.

This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.

Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!

Now back to the solution guide…

Creating the calculations

We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.

Create a parameter

pTop

Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20

Show this parameter on the sheet, and then create a calculated field

Order Date (Quarters)

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

Format to the YYYY QX style.  Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.

We need to identify customers who are in the top x for each quarter. Create a new calculated field

Is Top X Customer?

RANK(SUM([Sales]))<=[pTop]

Add to Rows and adjust the table calculation so it is computing by Customer Name only. 

We now want the Sales just for those customers who are in the top x, so create

Top X Sales

IF [Is Top X Customer?] THEN SUM([Sales]) END

Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.

We now need the total of these sales per quarter so create

Total Top X Sales

WINDOW_SUM([Top X Sales])

Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter 

We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create

Total Sales per Quarter 

WINDOW_SUM(SUM([Sales]))

Add this to the table and again adjust the table calculation to compute by Customer Name only.

Now we have these two figures we can calculate the percentage. Create a new calculated field

Sales % per Quarter 

[Total Top X Sales]/[Total Sales per Quarter]

Format this as a % to 1dp. Add to the table.

Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.

Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain 

Create a new field

Customer Index

INDEX() 

Convert this field to discrete (right click on the field).

Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales 

Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values). 

If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.

We’ve now got the core fields we need to build the viz.

Building the Viz

Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.

Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.

Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.

Make the chart dual axes and synchronise axes .

Finally, tidy up the chart by 

  • Adjusting the Tooltip 
  • Removing gridlines, zero lines and row/column dividers
  • Hide the right hand axis 
  • Fix the left hand axis to end at 1 (so the axis goes to 100%)
  • Edit the left hand axis title
  • Update the sheet title to reference the pTop parameter

Then add the viz to a dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you create a control chart?

Donna ColesLeave a comment

Sean set this table calculation based challenge this week. The key to this was for the reference line & bands to change if any of the non-date related filters were change, but for the values to remain constant regardless of the timeframe being reported. Based on this, I figured a table calculation filter would be necessary for the timeframe filter, as these get applied at the end of the ‘order of operations’.

I used the data set Sean provided in the challenge, to ensure I would get the same values to verify my solution. After connecting to the dataset, I did set the date properties on the data source so the week started on a Sunday, again to ensure the numbers aligned (in the UK, my preferences are set to Mondays).

Building out the calculations

Since we’re working with table calcs, I’m going to build the calcs into a table first, so we can verify the figures are behaving. Start by adding Order Date to Rows as a discrete (blue) pill at the Week level. Then add Sales.

Create a new field

Median

WINDOW_MEDIAN(SUM([Sales]))

and add this into the table. The value should the same for every row.

Create a new field

Std D

WINDOW_STDEV(SUM([Sales]))

and add this too – again this should be the same for every row.

But we want to to show the distribution based on +1 or -1 standard deviations, so create

Std Upper

WINDOW_AVG(SUM([Sales])) + [Std D]

and Std Lower

WINDOW_AVG(SUM([Sales])) – [Std D]

and add these to the table.

Each mark needs to be coloured based on whether it is greater than the upper band, so create

Sales above Std Upper

SUM([Sales]) > [Std Upper]

and add to the table on Rows

Add Category, Sub-Category and Segment to the Filter shelf, and show the filters. Adjust them to see that the table calculation fields change, although still remain the same across every row.

To restrict the timeline displayed, we need a parameter

pWeeks

integer parameter defaulted to 18

Show this parameter on the canvas. Then, to identify the weeks to keep, create a new field

Index

INDEX()

and convert it to discrete, then add to Rows to create a ‘row number’ for each row.

But, I want the index to start from 1 from the end of the table, so adjust the tableau calculation on the Index field so that is it computing explicitly by Week of Order Date and is sorted by Min Order Date descending

The first row in the table is now indexed with the greatest number, and if you scroll down, the last row should be indexed with 1.

With this we can now created

Filter – Date

[Index]<=[pWeeks]+1 AND [Index]<>1

I was expecting just to have [Index}<=pWeeks, but Sean’s solution excluded the data associated to the last week, I assume as it wasn’t a full week, so the above calculation resolved that.

Add this to the Filter shelf and set to True.

Changing the value in the pWeeks parameter only should adjust the number of rows displayed, but the values for the table calculated fields shouldn’t change.

Building the viz

On a new sheet, add Order Date as a continuous (green) pill at the week level to Columns and Sales to Rows.

Add another instance of Sales to Rows. Change the mark type of the Sales(2) marks card to circle. Make the chart dual axis and synchronise axis.

On the All marks card, add Median, Std Upper and Std Lower to the Detail shelf.

Add a reference line to the Sales axis, referencing the Median value. Set it to be a thicker darker line.

Then add another reference line but this time set it to be a band and reference the Std Lower and Std Upper fields, selecting a pale grey fill.

On the Sales(2) marks card, add Sales above Std Upper to the Colour shelf, and adjust to suit. Add a border to the circles. If required, adjust the colour and size of the line on the Sales marks card too.

Add the Category, Sub-Category and Segment fields to the Filter shelf, and show the filters. Show the pWeeks parameter. Then add Filter-Date to the Filter shelf. Adjust the table calculation as described above, then re-edit the filter to just show the True values

Finally tidy up by

  • remove row and column dividers
  • hide the right hand axis (uncheck show header)
  • edit the date axis and delete the title

Then add all the information to a dashboard and you’re good to go!

My published viz is here.

Happy vizzin’!

Donna

Visualising population by age and gender in a population pyramid

Donna ColesLeave a comment

For this week’s challenge, we’re still in community month, so guest poster, Anna Clara Gatti, provided us with this challenge – to represent the spread of population in a pyramid. Anna provided 3 levels to the challenge, so I’m going to write this blog, building on each challenge.

Beginner Challenge

Defining the calculations

To start this we need to create some calculations. Firstly we have measures Male and Female which provide us with the population for each of the associated genders. But we need to ascertain the total population for each Country and Year so we can then calculate a % of total. So we need

Total Population

{FIXED [Country], [Year]: SUM([Male]) + SUM([Female])}

and from this we can then define

Male – % of Total Population

SUM([Male])/SUM([Total Population])

format this to a % with 1 dp

Female – % of Total Population

SUM([Female])/SUM([Total Population])

format this to a % with 1 dp

These are the core measures we want to plot, but we want to plot this against the Age. But the Age provided is ‘text’ and not a pure number. So to make life easier, we’re going to extract the number out

Age Axis

IF [Age] = ‘Less than 1 year’ THEN 0
ELSEIF [Age]= ‘100+ years’ THEN 100
ELSE INT(LEFT([Age],FIND([Age],’ ‘,1)-1)) END

Move this into the Dimensions section of the data pane (above the line ).

Now we can start to build the required viz.

Building the Pyramid

On a new sheet add Country to the Filter shelf and select Italy. Add Year to the Filter shelf. By default it will add as a green continuous pill. Just click OK to the filter dialog presented.

Then right click on the Year pill in the Filter shelf, and select discrete. Another filter selection dialog will appear. Select 2024.

Add Age Axis to Rows and change to be a continuous (green) pill. Add Male – % of Total Population to Columns, and then add Female – % of Total Population to Columns too.

Change the mark type on the All marks card to bar and reduce the size a bit. Add Measure Names to Colour and adjust accordingly.

Right click on the Male – % of Total Population axis and

  • Fix the axis from 0 to 0.01
  • Reverse the scale
  • Change the axis title

Then edit the Female – % of Total Population axis and fix the scale from 0 to 0.01 and rename the axis title to give you a symmetrical display

Add the following fields to the Tooltip of the All marks card, and then update accordingly :

  • Male
  • Female
  • Female – % of Total Population
  • Male – % of Total Population
  • Age

Finally tidy up the display by removing row & column dividers, hiding the Age Axis (right click > uncheck show header) and removing the row gridlines.

Building the KPI

On a new sheet add Total Population to Text. Revert back to the Pyramid sheet, and set the both the filters to also apply to new KPI sheet you’re creating.

Update the text to include the ‘Total Population’ label and adjust size of font and align middle centre. Change the mark type to shape and select a transparent shape (for details on how to do this refer to this blog). Set the sheet to Entire View then apply a grey background. Ensure the Tooltip doesn’t show.

Creating the dashboard

Add the Pyramid chart and the KPI chart to a dashboard. I used a vertical layout container to organise the ‘rows’ , and then placed horizontal containers within, so I had a ‘row’ to show the filter controls, and a ‘row’ to show the KPI and then a ‘row’ to display the chart.

In the ‘row’ showing the KPI I added a text object alongside and used the wingdings character set to display a square symbol which I could then just colour to represent the legend.

My beginner version of the viz is here.

Intermediate Challenge

Colouring the pyramid

For this challenge, I started by simply duplicating the Pyramid built for the Beginner challenge. I then created a new field

Age Bracket

IF [Age Axis] <=14 THEN ‘Young’ ELSEIF [Age Axis] >= 65 THEN ‘Elder’
ELSE ‘Active population’
END

and added this to the Detail shelf of the All marks card. I then added this to be on the Colour shelf as well as Measure Names by updating the symbol to the left of the pill

Adjust the colours on the colour legend accordingly, and then update the Tooltip to include a reference to the Age Bracket.

Building the dashboard

Duplicate the original dashboard you built, then swap the pyramid chart by clicking on the Pyramid chart object in the dashboard. Then on the left hand side in the dashboard pane, click on the 2nd pyramid sheet, and click the ‘swap’ icon to replace the chart..

Remove the title that’s automatically added. Then update the legend text box.

My Intermediate version is here.

Advanced Challenge

Defining the additional fields

For this challenge, we’re going to need a lot more fields, including the following parameters

Country 1

string parameter, that is a list, and is populated from the Country field when the workbook is opened. Defaulted to Italy.

Country 2

As above but defaulted to Austria

Year 1

integer parameter, that is a list that is populated from the Year field when the workbook is opened. Defaulted to 2024 that is formatted to display as a number with 0 dp and no thousands separator.

Year 2

As above, also defaulted to 2024.

With these parameters, we also need to redefine the the total population values

Total Pop – Year & Country 1

IF [Year] = [Year 1] and [Country] = [Country 1] THEN [Total Population] END

Total Pop – Year & Country 2

IF [Year] = [Year 2] and [Country] = [Country 2] THEN [Total Population] END

and we also need to capture the male and female populations for these parameters

Male Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Male] END

Male Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Male] END

Female Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Female] END

Female Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Female] END

and then in turn, we redefine the % of total calculations

Male – % of Total 1

SUM([Male Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Male – % of Total 2

SUM([Male Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Female – % of Total 1

SUM([Female Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Female – % of Total 2

SUM([Female Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Set all the % of total calcs to be % with 1 dp.

Building the Pyramid

Once again start by duplicating the pyramid chart sheet built for the intermediate solution

Drag Male – % of Total 1 and drop it directly onto the existing Male = % of Total Population pill on the Columns shelf, so it basically replaces it.

Repeat the process by dragging Female – % of Total 1 and dropping it directly over Female – % of Total Population. Adjust the colours if required.

Remove the Country and Year field from the Filter shelf and show the 4 parameters.

Drag Male – % of Total 2 and add to Columns between the two existing pills.

On the Male – % of Total 2 marks card, change the mark type to Line , remove Measure Names from Colour and move Age Bracket to Tooltip. Change the colour to dark grey/black.

Make the % of total male pills dual axis and synchronise the axis.

Repeat the process by adding Female – % of Total 2 to Columns to the right hand side of the existing Female field, adjusting the mark type and colouring and making dual axis.

Right click on the top axis and uncheck show header to hide them. Then right click on the Male – % of Total 1 axis at the bottom, as as before, fix the axis from 0 to 0.01, reverse the axis, and update the title. Repeat for the Female axis (though don’t reverse).

On the All marks card, make sure all the following fields exist on the tooltip, then adjust the tooltip as required, referencing the parameters as well.

  • Male Pop – Year & Country 1
  • Male Pop – Year & Country 2
  • Female Pop – Year & Country 1
  • Female Pop – Year & Country 2
  • Male – % of Total 1
  • Male – % of Total 2
  • Female – % of Total 1
  • Female – % of Total 2
  • Age
  • Age Bracket

Building the KPI cards

Duplicate the original KPI card

Drag Total Pop – Year & Country 1 directly onto the existing Total Population field. Then add the Country 1 and Year 1 parameters to the Label shelf, and update the text as required. Remove the fields from the Filter shelf.

Repeat this process again but add the appropriate ‘2’ versions of the fields to create the 2nd KPI card.

Building the dashboard

Once again, duplicate the dashboard and swap the pyramid charts. Replace the filter controls (if still present) with a row of parameter controls.

Swap/add the KPI sheets, and add an additional Text object. Update the text to display the ‘legends’ as required.

I chose to add navigation buttons to my dashboard to move between the 3 versions of the challenge.

My advanced version is published here.

Phew! a lot going on here it seems. Hope you managed it all!

Happy vizzin’!

Donna

Can you compare to a selected months value?

Donna ColesLeave a comment

This week’s challenge by Lorna was to deliver some functionality without using LODs or table calculations. She hinted that parameters and parameter actions would be your friends.

Setting up the parameters

Create a parameter to capture the month selected

pMonth

date parameter defaulted to 01 April 2023

and then create one that will store the value associated to the month selected

pMonthSales

float parameter defaulted to 0

Building the Viz

On a new sheet add Order Date as a continuous (green) pill at the month-year level to Columns. Add Sales to Rows.

Create a new calculated field

Difference from Selected Sales

SUM([Sales]) – [pMonthSales]

and add this to Rows.

Change the mark type of this second marks card to bar and add Difference From Selected Sales to the Colour shelf. Adjust the colour to a diverging scale and centre at 0

Set the Size of the bars to be Manual rather than Fixed and adjust the slider to suit.

Add a reference line to the Order Date axis, that references the pMonth parameter.

Adjust the Tooltips, remove gridlines and add a title.

Adding the interactivity

Add the sheet to a dashboard. Add a dashboard parameter action

Set Date

On select of the viz, update the pMonth parameter with the value from the Month([Order Date]) field.

Add another dashboard action

Set Value

On select of the Viz, update the pMonthSales parameter with the value from the SUM(Sales) field that is aggregated at the SUM level.

Now if you click on a point on the line chart, the bottom bars should alter, but they’ll all appear ‘faded’ initially.

To resolve this, create a new calculated field

HL

“HL”

and add this to the Detail shelf of the All marks card. Then create a dashboard highlight action

Highlight

On select of the viz, highlight the HL field only

When you now click, the bottom marks aren’t faded as they essentially are all ‘highlighted’ too.

And that’s it for this week!

My published viz is here.

Happy vizzin’!

Donna

Can you show total sales and filtered sales in the same view?

Donna ColesLeave a comment

A guest post this week from Hannah Bartholomew asked us to show a filtered and unfiltered view of data in the same viz.

Building the calculations

We need to show the total sales and the total sales by Sub-Category which won’t be impacted by any filters. We can use a level of detail (LODs) for this.

Sales per Sub Cat

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

format to £ with 0 dp

Pop this and Sales into a table. The total value is identical.

Create a new field

Order Date (Year)

Year([Order Date])

Add Region, Customer Name, Manufacturer and Order Date (Year) to the Filter shelf and show all the filters (display as multiple value drop down to save space). If you now adjust any of the filter values, the Sales value will change, but Sales per SubCat won’t.

We need to show the % of total, so create

% Total

SUM([Sales]) / SUM([Sales per Sub Cat])

and format to % with 0 dp. Add to the table.

This essentially are the measures needed for the overall bar at the top of the display.

Now add Sub-Category to Rows. and you can see the data is now simply broken down to this level without any adjustments to the calculations.

If you reset all the filters back to ‘All’ the Sales per Sub Cat and Sales columns should match with 100% .

Lastly, we need to be able to filter by a start and end date. We need some parameters and additional calculated fields for this.

Create new fields

Max Date

{MAX([Order Date])}

Min Date

{MIN([Order Date])}

Create a new parameter

pStartDate

date parameter that refers to the Min Date field when workbook opens, and displays a range of values based on the values in the Order Date field when the workbook is opened.

Create another parameter pEndDate which is similar except it refers to the Max Date field when opened.

Show these 2 parameters. We then need

Filter – Order Date

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

Add this to the Filter shelf and set to True.

Then set all the filters to ‘apply to worksheets > all using this data source‘ so that when you create new sheets, they will get automatically added. Test changing the dates and the Sales and %Total will adjust.

Building the Total Sales Bar

On a new sheet, add Sales to Rows and add Sales per Sub Cat to Rows. Show the relevant filter controls and the two parameters.

Add % Total to the Tooltip shelf of the All marks card, then adjust the tooltip on each of the Sales and Sales per SubCat marks cards as required.

Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to be bar. Adjust the colours of the Measure Names legend to suit. Click the top axis and select move marks to back. Reduce the size of the Sales bar.

Hide the top axis, edit the title of the bottom axis, remove all column and row dividers, make the column axis rules & tick lines slightly more prominent. Edit the title of the sheet.

Building the Sub-Category by Sales Bar

The simplest way for this is to duplicate the above sheet. Then add Sub-Category to Rows, and apply a sort to sort by the Sales per Sub Cat field descending.

Then update the title on the sheet.

Building the ‘Filtered Options’ List

There is a section on the display that shows the values selected in the filters. For this, create a new sheet and then double click in to the area below the marks card and type ‘dummy’ (including quotes) and press enter/return. At this point a mark should display and all the filters get added.

Change the mark type to polygon. Then update the text of the sheet title to reference the filters. Format the background of the whole worksheet to be pale blue.

Now the sheets all need to be arranged on a dashboard. Use layout containers and outer/inner padding to help build the design. I just took a screen snip of the logo Hannah used and then added that as an image to my right hand vertical container. I also created another sheet using a similar ‘dummy’ technique but changed the mark type to shape and added a ‘help’ icon shape I already had.

My published viz is here.

Happy vizzin’!

Donna

Can you compare an Olympic medallist’s age to a user’s age?

Donna ColesLeave a comment

As the Paris 2024 Olympic Games continues, then #WOW continues with another Olympic themed challenge, this time set by Kyle.

Defining the core calculations

There’s a lot of LoD calcs used in this, so let’s set this up to start with.

Avg Age Per Sport

INT({FIXED [Sport]: AVG([Age])})

Avg Age Per Sport – Gold

INT({FIXED [Sport]: AVG(IF [Medal]=’Gold’ THEN [Age] END)})

Avg Age Per Sport – Silver

INT({FIXED [Sport]: AVG(IF [Medal]=’Silver’ THEN [Age] END)})

Avg Age Per Sport – Bronze

INT({FIXED [Sport]: AVG(IF [Medal]=’Bronze’ THEN [Age] END)})

Oldest Age Per Sport

INT({FIXED [Sport]: MAX([Age])})

Youngest Age Per Sport

INT({FIXED [Sport]: MIN([Age])})

Format all these fields to use Number Standard format so just whole numbers are displayed.

Let’s put all these out in a table – add Sport to Rows and then add all these measures

Kyle hinted that while the display looks like a trellis chart, it’s been built with multiple sheets – 1 per row. So we need to identify which row each set of sports is associated to, given there are 5 sports per row.

Row

INT((INDEX()-1)/5)

Make this discrete, and add this to Rows, and then adjust the table calculation so that it is explicitly computing by Sport, and we can see how each set of sports is ‘grouped’ on the same row.

Building the core viz

The viz is essentially a stacked bar chart of number of medallists by Age. But the bars are segmented by each individual medallist, and uniquely identified based on Name, Medal, Sport, Event, Year.

Add Sport to Columns, Age to Columns (as a continuous dimension – green pill) and Summer Olympic Medallists (Count) to Rows.

Change the mark type to Bar and reduce the Size a bit. Add Year, Event and Name to the Detail shelf, then add Medal to the Colour shelf and adjust accordingly. Re-order the colour legend, so it’s listed Bronze, Silver , Gold.

For the Tooltip we need to colour the type of medal, so need to create

Tooltip: Bronze

UPPER(IF [Medal] = ‘Bronze’ THEN [Medal] END)

Tooltip: Silver

UPPER(IF [Medal] = ‘Silver’ THEN [Medal] END)

Tooltip: Gold

UPPER(IF [Medal] = ‘Gold’ THEN [Medal] END)

Add all these to the Tooltip shelf, then adjust the Tooltip accordingly, referencing the 3 fields above and colouring the text.

The summary information for each sport displayed above each bar chart, is simply utilising the ‘header’ section of the display. We need to craft dedicated fields to display the text we need (note the spaces)

Heading – Medal Age

“Avg Medalling Age: ” + STR([Avg Age Per Sport])

Heading: Age per Medal

“Gold: ” + STR(IFNULL([Avg Age Per Sport – Gold],0)) + ” Silver: ” + STR(IFNULL([Avg Age Per Sport – Silver],0)) + ” Bronze: ” + STR(IFNULL([Avg Age Per Sport – Bronze],0))

Heading: Young | Old

“Youngest: ” + STR([Youngest Age Per Sport]) + ” Oldest: ” + STR([Oldest Age Per Sport])

Add each of these to Rows in the relevant order. Widen each column and reduce the height of each header row if need be

Adjust the font style of all the 4 fields in the header. I used

  • Sport – Tableau Medium 14pt, black, bold
  • Heading – Medal Age – Tableau Book 12pt, black, bold
  • Heading: Age per Medal – Tableau Book 10pt, black bold
  • Heading: Young | Old – Tableau Book 9pt, black

The title needs to reflect the number of medallists above a user defined age. To capture the age we need a parameter

pAge

integer parameter defaulted to 42

Then we can create

Total Athletes at Age+

{FIXED:COUNTD( IF [Age]>= [pAge] THEN [Name] END)}

Add this to the Detail shelf, then update the title of the viz to reference this field. Note the spacing to incorporate the positioning of the parameter later.

The chart also needs to show background banding based on the selected age. Add a reference line to the Age axis (right click axis > add reference line), which references the pAge parameter, and fills above the point with a grey colour

Ultimately this viz is going to be broken up into separate rows, but we need the Age axis to cover the same range. To ensure this happens, we need

Ref Line – Oldest Age

{FIXED: MAX([Oldest Age Per Sport])}

Add this to the Detail shelf, then add a reference line which references this field, but doesn’t actually ‘display’ anything (it’s invisible).

Tidy up the visual – remove gridlines, zero lines, row & column dividers. Hide the y-axis (uncheck show header). Remove the x-axis title. Hide the heading label (right click > hide field labels for columns). Hide the null indicator.

Filtering per row

Add the Row field to the Filter shelf and select the only option displayed (0). Then edit the table calculation and select all the fields to compute by, but set the level to Sport.

Show the Row filter and select the 0 option only. – only the first 5 sports are now displayed.

Name this sheet Row1. Then duplicate the sheet, set the Row filter to 1 and name the sheet Row2. Repeat so you have 7 sheets.

Building the dashboard

Use vertical containers to help position each of the 7 rows on the dashboard. For 6 of the sheets, the title needs to be hidden. I used a vertical container nested within another vertical container to contain the 6 sheets with no title. I then fixed the height of this ‘nested’ container and ‘distributed” the contents evenly. But I did have to do some maths based on the space the other objects (title & footer) used up on my dashboard, to work out what the height should be.

You’ll then need to float the pAge parameter onto your layout, and may find that after publishing to Tableau Public, you’ll need to edit online to tweak the positioning further.

My published viz is here.

Happy vizzin’!

Donna

Connected Scatterplots

Donna ColesLeave a comment

Sean set this challenge this week, to build a connected scatterplot to allow additional insights to be gained.

We need to show a circle per country related to the specified year, and then show the data for all years if a country is specified from the drop down, or ‘clicked on’ by the user; these data points are all then connected.

Let’s start by building up the various parameters and calculated fields needed to help with this.

Setting up the data

For the user inputs, I used parameters

pYear

Integer parameter, defaulted to 2000, displayed in a format so no thousand separators are shown. I populated the list using the values from the Year field.

pCountry

string parameter defaulted to All. I populated the list of entries by first adding values from the Country field. I then manually added an All entry to the bottom of the list and dragged it to the top. I could then set All as the default value.

On a new sheet, show these parameters.

We’re going to use a dual axis chart to display the viz, and for this, we’re going to get the relevant measures for the specific Year and for the specific Country.

To see what I’m aiming for, lets’ build out the data in a table. Add Country to Columns and Year to Rows. Display the values of Fertility Rate and Life Expectancy. This just gives us all the data points

But we only want the points related to the pYear (2000) or if pCountry if it’s not All (in this case Afghanistan).

So we create

Fertility Rate for Year

[Year] = [pYear] THEN [Fertility Rate] END

Life Expectancy for Year

IF [Year] = [pYear] THEN [Life Expectancy] END

format these to 1 dp and then add to the table. The fields only contain values for the specified pYear.

Create

Fertility Rate for Country

IF [Country] = [pCountry] THEN [Fertility Rate] END

Life Expectancy for Country

IF [Country] = [pCountry] THEN [Life Expectancy] END

format these to 1 dp and also add to the table. We now have these entries only existing for the selected pCountry.

If pCountry is set to All, the Fertility Rate for Country and Life Expectancy for Country are empty for every Country.

We now have the basics we need to build the viz.

Building the ScatterPlot

On a new sheet, show the parameters, then add Fertility Rate for Year to Columns and Life Expectancy for Year to Rows and Country to Detail. Change the mark type to circle. Adjust the Tooltip.

Create a new field

All Countries

[pCountry] = ‘All’

and add to the Colour shelf. Adjust the colours so when pCountry = All, the All Countries colour legend is True and displays a darker instance of a colour, as opposed to when pCountry is set to something else, and the All Countries colour legend is false.

Now add Fertility Rate for Country to Columns and Life Expectancy for Country to Rows. Change both fields to be Dimensions. On the 2nd marks card, add Year to Detail. and remove the All Countries field from the Colour shelf. Change the mark type to line and move Year to Path. Set the colour accordingly.

Then set both the Rows and Columns to be Dual Axis and synchronise both axis. Remove Measure Names from the colour shelf on the all marks card.

Adjust the Tooltip of the 2nd (line) marks card.

Add Year to the Label shelf of the 2nd marks card and update so it just displays for the Min & Max value. Adjust font size and style.

On the first marks card (the circle) add Country to Label and adjust so it only displays when selected.

Hide the right and top axis. Remove row & column dividers. Hide the null indicator and update the title of the axes. Name the sheet Scatterplot or similar.

Building the Dashboard

Add the sheet to a dashboard, and float the parameters into a suitable location. Add a floating text box that references the pYear parameter and position bottom left of the chart. Add a parameter action to update the pCountry parameter when a circle is clicked.

Click Country

On select of the Scatterplot Viz, set the pCountry parameter, passing in the value from the Country field. When cleared, set the parameter back to All.

Finally, if you click a circle to select a Country, you’ll find that the circles ‘fade out’ more than what you want – you want them to look the same as the colour when a country is selected via the dropdown. Essentially, you want all the circles to be ‘highlighted’ on click. To do this, create a new field

HL

“Highlight”

and add this to the Detail shelf on the scatterplot sheet. Then on the dashboard, add a Highlight action

HL marks on click

On select of the scatterplot viz, target itself, highlighting the HL selected field

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

Happy vizzin’!

Donna

Can you design a multi-dimensional Panel Chart for detailed analysis?

Donna ColesLeave a comment

Yusuke set this week’s challenge which is pretty tough-going. I got there eventually, but it wasn’t smooth sailing, and had a lot of false starts and changes to calculations throughout to get to the end. I will endeavour to explain where I had difficulties, but some of the calculations I came up with were more down to trial and error ( eg I wonder if this will work…?) rather than a known direction.

Modelling the data

We were provided with a version of Superstore and a State Abbreviations data set. I chose to use the Superstore Excel file I already had. I combined the two data sets in the data source pane using a relationship where State/Province in the Orders table of the Superstore excel file matched with the Full Name field in the State Abbreviations csv file.

As the data was just related to 2024, I added this as a data source filter (Year of Order Date = 2024)

Identifying the month

I decided I was going to use a parameter to select the month to filter. I first created

Order Date MY

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

and then created a parameter

pOrderDate

date parameter defaulted to 01 Dec 2024, that I populated as a list using the values from the Order Date MY field. I set the display format to be a custom format of mmmm yyyy

Examining the data

On a new sheet, add State/Province and Abbreviation to Rows and Category to Columns to create a very simple ‘existence table’.

The presence of the Abc in the text table, shows that at least 1 record exists for the State/Category combination during 2024.

We can see some states, such as Arkansas, District of Colombia, Kansas etc don’t have any records at all for some Categories. However, when we look at the viz, we can see markers and labels associated to these Categories. In the image below, Kansas shows markers and labels against Furniture and Technology, when there are no records for these combinations at any point in 2024.

Handling this situation is the reason some of the calculations that follow are more complicated than you might expect.

Building the Trellis/Panel Chart calculations

Now when I built the viz, putting it into a trellis display was the last thing I did, but in rebuilding in order to write the blog, I’m finding that the table calcs needed start to help with the missing marks discussed above.

We need to count the number of States. For this create

State Count

SIZE()

Make this discrete and add to Rows. Adjust the table calculation to compute by State/Province and Abbreviation. 47 should be listed against every row in the column which is the number of rows displayed, and an Abc mark now exists for every State/Category combination.

We’ll also create

Index

INDEX()

make this discrete and add to Rows as well adjusting the table calculation to also compute by State/Province and Abbreviation

This has the effect of providing a counter for every row from 1-47.

We also need a parameter to define how many columns the display will be over

pCols

integer parameter defaulted to 8 which displays a range from 1 – 15 with a step of 1

With these fields, we can now define which row each record will sit on based on the counter for each State (the Index) and the number of columns to display (pCols).

Rows

INT(([Index]-1)/[pCols])

and we can also work out which column each record will sit in

Cols

([Index]-1)%[pCols]

Make both these fields discrete and add to Rows. Verify the table calc setting is as before. Show the pCols parameter, and test changing it and observe how the Rows and Cols values change

Remove State Count and Index from Rows. These won’t be needed in the final viz, as they’re just building blocks for other calculated fields.

Calculating the Profit Ratios

We need two Profit Ratio values – one for each State and Category per month and one ‘overall’ for each State by month

At the State/Category level, create

Monthly Sales

IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END

and

Monthly Profit

IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END

and from this create

Monthly PR

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

custom format this to 0.0%;-0.0%;N/A which will then display the text N/A whenever the value is 0. Add this to the Text shelf.

Some additional State/Province records have appeared at this point with no Abbreviation (null). Filter these out.

To get the Overall profit ratio for the State, I created

State Sales for Month

{FIXED [State/Province]: SUM(
IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END)
}

and

State Profit for Month

{FIXED [State/Province]: SUM(
IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END)
}

and then created

PR for Month

SUM([State Profit for Month])/SUM([State Sales for Month])

and formatted this to a % with 1 dp.

Add PR for Month into the table and then apply a Sort to the State/Province field to sort by PR for Month descending

We’ve still got some gaps – we want to see the PR for Month value populated against every Category which have a profit ratio. For this create

State PR for Month

WINDOW_MAX([PR for Month])

Add this to the table and apply the table calculation to compute by Category only – we now have the overall profit ratio for each State plotted against every Category.

Note – we needed both PR for Month and State PR for Month as the latter is a table calculation and we can’t apply sorting on a table calc.

Building the panel chart

In building this I referred to my own blog post on a previous challenge, as the bar chart we need to build, isn’t a typical bar chart.

On a new sheet, add State/Province, Abbreviation and Category to Rows. Add Monthly PR to Text. Move the Abbreviation pill from Rows to Filters and exclude Null. Sort the State/Province field by PR for Month descending. Show the pOrderDate and pCols parameters.

Add Cols to Columns and Rows to Rows, so it’s the first pill listed. Move State/Province to Detail. Adjust the table calculations of both the Rows and Cols fields to be computing by State/Province and Category (listed in that order), and at the level of State/Province.

To build the bars, we’re using ‘fake axis’ on both the x & y axis to position the marks we want to display. We need

PR Bar Axis

ZN(LOOKUP(MIN(0.0),0))

and

Y-Axis

IFNULL(LOOKUP(MIN(0.5),0),0.5)

As alluded to above, these evolved as I played around to get the behaviour required.

Add Y-Axis to Rows and adjust the table calculation to compute by State/Province and Category (in that order) at the level of State/Province. Edit the axis to be fixed from -1 to 4.

Add PR Bar Axis to Columns. Again adjust the table calculation setting to be as the others. Change the mark type to Bar. Add another instance of Monthly PR to the Size shelf, and then adjust the Size to be Fixed and aligned Left.

Add Monthly PR to the Colour shelf, and adjust the colour legend to be fixed from -1.5 to 1.5 and centred at 0.

Edit the PR Bar Axis axis to be fixed from -1.25 to 1.25, to ensure the bars remain centred aligned regardless of the month selected.

Adjust the Tooltip as required.

Format the chart to remove all gridlines, remove the zero line for rows, and make the zero line for columns more prominent – dashed and darker. Remove all axis ticks. Add dark row and column dividers against the pane only and at the appropriate level so the information for a single State is contained within the boundaries.

Add row banding against the pane only at the appropriate band size and level

Reduce the width of each row, and widen each column to get a ‘squarer’ display.

Now we need to add the State/overall profit square. for this we need to ‘position’ a mark on the viz

Square Point

IF LAST()=0 THEN 0.9 ELSE NULL END

Add this to Columns. Edit the table calculation so it’s just computing by Category, then make the chart dual axis and synchronise axis.

This has created a second marks card, and essentially ‘duplicated’ the information for the ‘last’ Category only in each State.

Change the mark type to square. Remove Monthly PR from Colour and Size and Label . Add State PR for Month to Label and Colour instead. Adjust the table calculations to be computing by Category only. Align the label to be middle centre. Increase the Size of the mark so it’s roughly square.

Adjust the Colour legend of the State PR for Month so it ranges from -1.5 to 1.5 like the other one.

Adding Abbreviation to Label doesn’t work for every cell, so we need

Label – Abbreviation

WINDOW_MAX(MAX([Abbreviation]))

Add to Label and adjust the table calculation to be computing by Category only. Adjust the layout of the Label to match.

Update the Tooltip to reference State PR for Month instead. Hide the null indicator.

Finally hide the axis and the Cols and Rows pills (right click, uncheck show header). Hide the Category column title. Name the sheet.

And that should be the main viz. Add to a dashboard and only show one of the colour legends, renamed accordingly.

My published viz is here.

Happy vizzin’!

Donna