Category: #WorkoutWednesday

Can you account for nulls in your averages?

Donna ColesLeave a comment

In this week’s challenge Yusuke wanted us to ensure filtering by date didn’t actually exclude any dimension values (so null values displayed as 0) and average calculations then accounted for those null value entries too.

Setting up the core data requirements

Yusuke provided a link to a version of Superstore which I used, since the requirements included the Manufacturer field which isn’t in the usual Excel file. I first created the hierarchy of Category > Sub-Category > Manufacturer.

Category Hierarchy

Right-click the Category field and select Hierarchy > Create Hierarchy. Name it Category Hierarchy, then drag Sub-Category and Manufacturer to be positioned under the Category field.

The display shows the number of orders, so we need

#Orders

COUNTD([Order ID])

Add Category to Rows and then expand to display Sub-Category and Manufacturer. Add #Orders to Text and add Order Date as a discrete (blue) pill at the Weekday level. This table highlights the ‘gaps’ which we need to display as 0. It also shows us how many rows of data we should always expect regardless of the date being filtered.

A standard ‘quick filter’ on date will just remove the rows that aren’t included in the filter, so we need to handle the date filtering using parameters.

pMinDate

date parameter defaulted to 11 Jul 2025

and

pMaxDate

date parameter defaulted to 23 Jul 2025

with these we create

#Orders in Date Range

ZN(COUNTD(IF [Order Date]>=[pMinDate]AND [Order Date]<=[pMaxDate]THEN [Order ID] END))

Add this into the table, and we can see we still have blank entries.

Now the trick here, which I have to admit I just couldn’t resolve until I looked at Yusuke’s solution, is to create a new field

Index

INDEX()

and add this to the Detail shelf, and all the gaps in the #Orders in Date Range measure will be replaced by 0.

Adding the average

Move the #Orders from the Measure Values section onto Tooltip.

The add column totals (Analysis menu > Show All Subtotals). Then go into the menu again and select Total All Using > Average. You’ll have totals at the Manufacturer level and the Sub-Caetgory level

Right click on the Total label in the Manufacturer column, and Format. In the left hand pane, update the Label to read Avg.

Repeat the same by formatting the Total label against the Sub-Category column.

Now format the numbers displayed by right-clicking on the #Orders in Date Range field on the Text shelf and formatting. In the left hand pane, select the Pane tab and set the format of the Numbers in the Default section to standard and the format of the Numbers in the Totals section to 2dp.

Formatting the rest of the table

Add #Orders in Date Range to Colour. Change the mark type to Square. Edit the Colour palette and select a diverging palette (eg red-blue-white diverging) but set the centre to 0 and check the include totals checkbox.

Format the table, and select the shading tab. Set the Total header to pale orage, and row banding to pale grey at band size 1.

Then select the borders option and set the default options against cell, pane and header to dark grey. Then add thicker orange borders against the totals, and remove row dividers. Add grey column dividers.

Hide the Order Date column heading (right click the Order Date label and hide field labels for columns). Right click the Order Date pill in Columns and format; set the Dates option to display abbreviation

Format the font of all columns to be the same (I used Tableau Medium, black).

We want to display a * to indicate null values, so create

Number prefix *

IIF(([#Orders in Date Range])=0,’*’,NULL)

Add to the label shelf and adjust the position of the fields on that shelf.

The create

Tooltip – 0 orders

IIF([Number Prefix ]= ‘‘, ‘* No orders found for this period’, NULL)

and add to the Tooltip shelf and adjust the Tooltip to suit. Then add Category to filter and select all options.,

Collages / expand the viz and adjust the dates to test the functionality and display.

Creating the date filter & Apply button

On a new sheet, double click in to the space below the Marks shelves and the type ‘Apply’. Move the field created from Detail to Label. Change the mark type to square, adjust the size to be as large as possible and then set the fit to entire view. Format the Apply label to be centred and larger font.

Add Order Date to the Filter shelf, select range oi dates and enter values from 11 Jul 2025 to 23 Jul 2025. Show the filter, and the add the Order Date filter to context.

Create new fields

Min Date

MIN([Order Date])

and

Max Date

MAX([Order Date])

and add both to the Detail shelf.

Create a new field

Colour

[Min Date]= [pMinDate] AND [Max Date]= [pMaxDate]

And add to the Colour shelf. Adjust the colour of the true option to pale grey. Then change the value in the Order Date filter, so the colour shows as false and adjust colour to orange. Hide the tooltip.

Finally create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf.

Building the dashboard

Use layout containers with padding to add the table viz and the apply button viz to a dashboard. Show the Category filter for the table viz, and the Order Date filter for the apply button viz. Below is how I arranged my layout containers

Create the following dashboard actions:

Set Min Date

On select of the Apply Button viz, target the pMinDate parameter passing in the value from the Min Date field.

Set Max Date

On select of the Apply Button viz, target the pMaxDate parameter passing in the value from the Max Date field.

Deselect Button

On select of the Apply Button viz on the dashboard, target the Apply Button sheet directly, selecting the fields True = False.

Finally add a floating text box to provide a key for the * indicator.

My published viz is here.

Happy vizzin’!

Donna

KPI Trend Monitor With Period Comparison

Donna ColesLeave a comment

For the challenge this week, Yoshi wanted us to recreate this summary KPI card detailing the latest Profit Ratio, the change from the previous day, and comparisons against equivalent timeframes.

Defining the calculations

In a usual scenario, we would utilise the TODAY() function to base the rest of the measures being displayed. However given the dataset we’re using and the desire to ensure the viz doesn’t eventually display nothing on my Tableau Public page, ‘Today’ will be harded coded in the parameter

pToday

date parameter defaulted to 04 Feb 2025.

We have 3 timeframes we need to visualise, so we need to find the dates these relate to

Today Last Month

DATE(DATEADD(‘month’, -1, [pToday]))

Today Last Year

DATE(DATEADD(‘year’, -1, [pToday]))

I also want to group these ‘timeframes’ and created

Recent | Prior Mth | Prior Yr

IF [Order Date] >= DATEADD(‘day’, -14, [pToday]) AND [Order Date] <= [pToday] THEN ‘Recent’ ELSEIF [Order Date] >= DATEADD(‘day’, -14, [Today Last Month]) AND [Order Date] <= DATEADD(‘day’, 14, [Today Last Month]) THEN ‘Prior Month’ ELSEIF [Order Date] >= DATEADD(‘day’, -14, [Today Last Year]) AND [Order Date] <= DATEADD(‘day’, 14, [Today Last Year]) THEN ‘Prior Year’
ELSE NULL
END

Add this to Rows on a sheet , then add Order Date as a discrete exact date (blue pill). Add Recent | Prior Mth | Prior Yr to Filter and exclude Null. All the dates we want to plot will be displayed against their relevant ‘category’

Create a new field

Profit Ratio

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

and format to % with 1 dp

Additionaly create fields

PR -Recent

IF MIN([Recent | Prior Mth | Prior Yr]) = ‘Recent’ THEN [Profit Ratio] END

and

PR – Not Recent

IF MIN([Recent | Prior Mth | Prior Yr]) <> ‘Recent’ THEN [Profit Ratio] END

and format these to % with 1dp too.

Add these 3 fields into the table

Finally we need to create the field for the x-axis based on the number of days, so create

X-Axis

CASE [Recent | Prior Mth | Prior Yr]
WHEN ‘Recent’ THEN DATEDIFF(‘day’,[pToday],[Order Date])
WHEN ‘Prior Month’ THEN DATEDIFF(‘day’,[Today Last Month],[Order Date])
WHEN ‘Prior Year’ THEN DATEDIFF(‘day’,[Today Last Year],[Order Date])
END

Add this onto Rows

Now we have the core fields needed for the line chart.

Building the Line Chart

On a new sheet, add Recent | Prior Mth | Prior Yr to Filter and exclude NULL. Then add X-Axis as a continuous (green) pill to Columns and PR – Recent to Rows. Add Recent | Prior Mth | Prior Yr to Colour. The add PR-Not Recent to Rows. Make the chart dual axis, synchronise the axis and then remove Measure Names from the All marks card. Adjust the colour legend associated to the Recent | Prior Mth | Prior Yr field.

Reorder the pills on the Rows so PR-Not Recent is first, which makes the ‘recent’ line in front of the others.

On the PR-Not Recent marks card, adjust the Path, to be a dashed line. On the PR-Recent marks card, add circle line markers (via the colour shelf options). Hide the right hand axis, and hide the null indicator

On the All marks card, add Profit Ratio to the Tooltip shelf and also add Order Date as a discrete exact date (blue bill) to Tooltip. Adjust to suit.

The Hide the X-Axis (uncheck show header), remove the title of the Y-axis, remove row & column dividers and vertical gridlines.

Building the KPI Card

Create new fields

PR-Today

{FIXED:SUM(IF [Order Date]=[pToday] THEN [Profit] END)}/{FIXED:SUM(IF [Order Date]=[pToday] THEN [Sales] END)}

format to % with 1 dp

PR-Yesterday

{FIXED:SUM(IF [Order Date]=DATEADD(‘day’, -1,[pToday]) THEN [Profit] END)}/{FIXED:SUM(IF [Order Date]=DATEADD(‘day’, -1,[pToday]) THEN [Sales] END)}

format to % with 1 dp

PR – Difference

[PR – Today] – [PR – Yesterday]

format to % with 1 dp

PR Direction Up

IF [PR Difference]>=0 THEN ‘Up’ END

PR Direction Down

IF [PR Difference]<0 THEN ‘Down’ END

On a new sheet add PR Difference, PR Direction Up, PR Direction Down, PR-Today and pToday to the Text shelf. Set the mark type to shape and use a transparent shape (see here for details on how to set this up). Then adjust the text as required, formatting as necessary and add a title that references pToday

The add these two sheets to a dashboard and you’re done. My published viz is here.

Happy vizzin’!

Donna

Can you create a dynamic moving average chart?

Donna ColesLeave a comment

Lorna set a table calculation focused challenge this week.

Creating the parameters

The challenge requires 3 parameters

pTimePortion

string list parameter containing values week, month and quarter; defaulted to month. Note the capitalisation of the display as value. The value itself should all be lowercase as it will be referenced in calculations later.

pTimeFrame

integer parameter ranging from 12 to 36, with a step-size of 6 and defaulted to 24

pMoveAvg

integer parameter ranging from 3 to 12 with a step size of 3 and defaulted to 3

Creating the calculations

The viz needs to display a dynamic date based on the value of the pTimePortion parameter.

Display Date

DATE(DATETRUNC([pTimePortion],[Order Date]))

It also displays the moving average of Sales based on the pMoveAvg parameter.

Moving Avg

WINDOW_AVG(SUM([Sales]), -1*([pMoveAvg]-1), 0)

Note, as the moving average is to include the Sales value of the ‘current’ date, then we need to subtract 1 from the pMoveAvg parameter. Ie if the pMoveAcg parameter = 3, then we want to calculate the moving average over the ‘current’ mark plus the previous 2 marks, so -2 needs to be fed into the calculation.

Finally, we need to restrict the dates being displayed in the viz. For this I calculated

Latest Date

WINDOW_MAX(MAX([Display Date]))

then fed this into

Date to Display

MIN([Order Date])>DATEADD([pTimePortion],-1*[pTimeFrame],[Latest Date])

ie only include records where the Order Date is greater than pTimeFrame weeks/months/quarters prior to the Latest Date.

Building the viz

On a new sheet, display all the 3 parameters. The add Display Date as a continuous exact date (green pill) to columns and Sales to rows.

Add Moving Avg to rows, make dual axis and synchronise the axis. Adjust colours of the marks to suit.

Add Date to Display to the Filter shelf and set to True. Then add Sales and Moving Avg to the Tooltip of the All marks card, and adjust Tooltip accordingly.

Update the title of the sheet so it references the various parameters

Finally, tidy up by

  • removing row and column dividers
  • hiding the right hand axis (right click, uncheck show header)
  • editing the Display Date axis, so the title of the axis references the pTimePortion parameter (Note – I did find this gets ‘lost’ when publishing to Tableau Public, so I had to re-edit my viz after publishing to reapply this setting).

Then add to a dashboard, and use a horizontal layout container to organise the parameters across the top. My published viz is here.

Happy vizzin’!

Donna

Can you create KPI cards with viz extensions?

Donna ColesLeave a comment

Kyle set the challenge this week to demonstrate the use of some of the free Viz Extensions available for Tableau Desktop and Tableau Public.

Creating the Sales KPI Card

After connecting the Superstore dataset, format the Sales field to be $ with 0 dp. Then on a new sheet, select the Add Extension option from the Marks drop down

In the resulting pop-up window, search for KPI and select the KPI Card (BANs) extension by LaDataViz

then click Open on the resulting window, and OK to the Allow Extension dialog displayed. You will then be presented with details on how to use the extension

Drag Sales onto KPI and add Order Date to Time, changing the level to a continuous month (green pill).

Then click Format Extension and set each tab as below (I’ve highlighted the options I changed)

which results in

Update the Tooltip as required, then rename the sheet Sales KPI or similar

Creating the Orders KPI card

Create a new field

# Orders

COUNTD([Order ID])

Duplicate the Sales KPI sheet, then add # Orders to KPI instead of Sales. Format the extension so the KPI name is Orders (KPI tab) and remove the $ prefix from the number (Labels tab). Adjust the Tooltip as required and rename the sheet Orders KPI or similar

Creating the Profit Ratio KPI Card

Create a new field

Profit Ratio

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

and format to % with 1 dp

Duplicate the Sales KPI sheet, then add Profit Ratio to KPI instead of Sales. Format the extension so the KPI name is Profit Ratio (KPI tab) and change the number format style to percentage with 1 decimal (Labels tab). Adjust the Tooltip as required and rename the sheet Profit Ratio KPI or similar

Creating the Filter Control

On a new sheet, select the Add Extension from the marks dropdown, and this time when prompted find the Drill Down Filter extension by LaDataViz

Then add Category and Sub-Category to the Detail shelf. Then Format Extension. I just changed the font colour (Label tab) and background colour (sheet tab)

Building the dashboard

Create a dashboard. Format the dashboard to set the background colour – I set it to a mid-dark grey (#666666) via the Format > Dashboard from the menu.

Add a horizontal container, and add the 3 KPI card sheets side by side. Set the container to distribute contents evenly.

For each KPI card object within the container, adjust the outer padding so there’s 10pt around and between each object (depending which object you’re adjusting the left/right padding may be 10 or 5pt), set the inner padding to 5pt, and set the background to match the background you used on the sheets (I used #555555).

Add the Filter sheet as a floating object, position as required and then add the Show/Hide button via the context menu of the object. Move the floating button as required too

Add a dashboard filter action

Filter

On select of the Filter sheet, target the 3 KPI sheets, excluding all values when selection is cleared.

Add any additional dashboard content – title etc, and you should now have a working filterable dahsboard

My published viz is here

Happy vizzin’!

Donna

Can you create a fake Viz in Tooltip?

Donna ColesLeave a comment

It was my turn to set the 1st challenge for #WOW2026, and I went back in time to recreate something from over 10 years ago! I was at #DataFam Europe in December and the marvellous Andy Cotgreave represented Team DataFam at the tips and tricks battle against Team Tableau, and showcased this technique which he originally blogged about a long time ago. But for a lot of the audience, this was something they hadn’t seen before, so I figured I’d share it here too (as I’d never actually tried it out myself back then). This technique is limited in it’s use – it only really works if you are depicting a % value, and are using a dimension which has a small number of different, but well known values (in this case Segment only has 3 options which don’t change) – but it is a lot faster to render ‘on hover’ than the traditional VIT.

Build the basic map

Connect to the Superstore dataset and construct the basic filled map

  • Double click on State/Province to automatically generate a map of the US (set the Country/Region to USA via the Map > Edit Locations menu option if the US isn’t initially displayed)
  • Add Sales to Colour to change to a filled map
  • Format Sales to $ with 0dp
  • Edit the background layers of the map (Map > Background layers menu option) to remove all the pre-selected options, to produce a ‘clean’ outline of the US on a white background.
  • Hide the map option which allow you to pan/zoom etc (Map > Map Options menu option and uncheck all the preselected options)
  • Hide the unknown indicator in the bottom right (right-click > hide indicator)

Create the calculations

We need to capture the % sales for each Segment per State into it’s own ‘segment specific’ field (which is why this technique could be a bit onerous if you had a high cadence of values in your chosen dimension).

Consumer % Sales

(SUM(IIF([Segment]=”Consumer”, [Sales],0))/SUM([Sales]))*100

Typically, when working with a % value, we would just do SUM(IIF([Segment]=”Consumer”, [Sales],0))/SUM([Sales]), then format this to a %. The value actually stored in the calculation would be a number <= 1. But we need the number to be between 0 and 100 for a step later on, so we perform the % conversion in the calculation by multiplying by 100. To display as a %, the field is just then formatted to a number with 0 dp and with a % suffix

Corporate % Sales

(SUM(IIF([Segment]=”Corporate”, [Sales],0))/SUM([Sales]))*100

format as above

Home Office % Sales

(SUM(IIF([Segment]=”Home Office”, [Sales],0))/SUM([Sales]))*100

If you pop these fields onto a new sheet and build a tabular view, you can see them working

Now we know the % values for each Segment, we can create a calculation to represent the bars. Firstly copy the █ unicode block into notepad or similar. Then paste 9 more instances directly alongside. The copy all 10 instances and paste that another 10 times, so you have a string of 100 blocks like below

████████████████████████████████████████████████████████████████████████████████████████████████████

Create a parameter

100 Blocks

string parameter which contains this 100 block string of characters

Create a new field for each Segment which represents the ‘bar’ of % sales

% Consumer Bar

LEFT([100 Blocks],ROUND([Consumer % Sales]))

% Corporate Bar

LEFT([100 Blocks],ROUND([Corporate % Sales]))

% Home Office Bar

LEFT([100 Blocks],ROUND([Home Office % Sales]))

This is basically truncating the string of 100 blocks to the number of characters represented by the % value. So if the % value is 39%, the result will be a string of 39 blocks.

Add Consumer % Sales, Corporate % Sales, Home Office % Sales, % Consumer Bar, % Corporate Bar and % Home Office Bar to the Tooltip shelf then edit the Tooltip and arrange the layout like the visual below, colour coding the % Bar and % Sales values for each Segment as you wish (make sure you use <tab> on your keyboard after the Segment label to ensure the bars all start at the same point on each row.

Resolve wrapping

On testing this, I found that some lines in the Tooltip wrapped the text onto another line depending on how long the line was. And I couldn’t control the width of the window the tooltip would display in to prevent this.

I figured I needed to make all the lines shorter, so need to adjust the bar calculation to halve the length of the ‘bars’, but to be accurate, I need to incorporate the smaller block character

Half block

string parameter storing the single value of  ▌

and then adjust the fields

% Consumer Bar

//to reduce length of bars, only display half the number of blocks – so 100% shows 50 blocks
//but if 99% need to show 49 1/2 blocks, so add on the half block when number is odd (%2=1)
IF ROUND([Consumer % Sales]) = 1 THEN [Half block]
ELSEIF ROUND([Consumer % Sales])%2 =1 THEN LEFT([100 Blocks],(ROUND([Consumer % Sales])/2)) + [Half block]
ELSE LEFT([100 Blocks],(ROUND([Consumer % Sales])/2)) END

% Corporate Bar

//to reduce length of bars, only display half the number of blocks – so 100% shows 50 blocks
//but if 99% need to show 49 1/2 blocks, so add on the half block when number is odd (%2=1)
IF ROUND([Corporate % Sales]) = 1 THEN [Half block]
ELSEIF ROUND([Corporate % Sales])%2 =1 THEN LEFT([100 Blocks],(ROUND([Corporate % Sales])/2)) + [Half block]
ELSE LEFT([100 Blocks],(ROUND([Corporate % Sales])/2)) END

% Home Office Bar

//to reduce length of bars, only display half the number of blocks – so 100% shows 50 blocks
//but if 99% need to show 49 1/2 blocks, so add on the half block when number is odd (%2=1)
IF ROUND([Home Office % Sales]) = 1 THEN [Half block]
ELSEIF ROUND([Home Office % Sales])%2 =1 THEN LEFT([100 Blocks],(ROUND([Home Office % Sales])/2)) + [Half block]
ELSE LEFT([100 Blocks],(ROUND([Home Office % Sales])/2)) END

Ta-dah!

Note – I did find that Wyoming with a 100% bar still wrapped on Desktop, but was fine when published to Tableau Public.

I finished up by removing row & column dividers, updating the title of the sheet, and then adding the sheet to a dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you create breadcrumbs for filters?

Donna ColesLeave a comment

Lorna set the challenge this week to display ‘breadcrumbs’ related to the filter selections.

Start by building out the basic viz :

  • Category and Sub-Category on Rows
  • Segment and Sales on Columns
  • Order Date (at the Year level), Ship Mode and Region all set to ‘All’ on Filter
  • Profit on Colour.
  • Show the filters on the display
  • Name the sheet Viz or similar

For the breadcrumbs, we need to understand how many of the options in each filter have been selected

Count Ship Modes

COUNTD([Ship Mode])

Count Regions

COUNTD([Region])

Count Years

COUNTD(YEAR([Order Date]))

and then we need to determine what to display based on the counts

BC – Ship Mode

IF [Count Ship Modes] = 1 THEN MIN([Ship Mode]) + ‘ (‘ + STR([Count Ship Modes]) + ‘)’
ELSEIF ([Count Ship Modes]) = SUM({FIXED:COUNTD([Ship Mode])}) THEN ‘All Ship Modes (‘ + STR([Count Ship Modes]) + ‘)’
ELSE ‘Multiple Ship Modes (‘ + STR([Count Ship Modes]) + ‘)’
END

BC – Regions

IF [Count Regions] = 1 THEN MIN([Region]) + ‘ (‘ + STR([Count Regions]) + ‘)’
ELSEIF ([Count Regions]) = SUM({FIXED:COUNTD([Region])}) THEN ‘All Regions (‘ + STR([Count Regions]) + ‘)’
ELSE ‘Multiple Regions (‘ + STR([Count Regions]) + ‘)’
END

BC – Years

IF [Count Years] = 1 THEN STR(MIN(YEAR([Order Date]))) + ‘ (‘ + STR([Count Years]) + ‘)’
ELSEIF ([Count Years]) = SUM({FIXED:COUNTD(YEAR([Order Date]))}) THEN ‘All Years (‘ + STR([Count Years]) + ‘)’
ELSE ‘Multiple Years (‘ + STR([Count Years]) + ‘)’
END

On a new sheet, double click into the section beneath the marks card and create an ad-hoc dimension called ‘dummy’. Add BC-Region, BC- Ship Modes and BC – Years to the Detail shelf. From the Viz sheet you built the chart on, select each filter and set to Apply to Worksheets and select the new sheet. Show all the filters.

Change the mark type to polygon which will make the displayed mark disappear. Then update the sheet title referencing the breadcrumb fields we created. Test the functionality by changing the filter values. Name the sheet BC or similar.

On a dashboard, use a Vertical layout container and add a text field for the title, the BC sheet underneath (set to fit entire view), then add a horizontal layout container and move/add the filters into it. Then add the Viz sheet below, also set to fit entire view.

Format the horizontal container with a grey background then select the option to add hide/show button to it. The button will automatically be a floating object, so move it to where you want it to be. And then that should be it.

My published viz is here.

Happy vizzin’!

Donna

Can you make a bar chart race?

Donna ColesLeave a comment

Yoshi & Yusuke presented this week’s #WOW2025 challenge live at the Agentforce World Tour in Tokyo. The key feature of this challenge is utilising the Pages shelf to step through how the shipment rankings for a set of products changes on a month-by-month basis.

Gathering the required files & modelling the data

I downloaded the Sample Super factory (subset).xlsx file from the provided google drive and connected directly to it. Relate Production_Plan_Output_Shipment to Product Master via the Product ID fields. I then added a Data Source Filter to restrict the data to Date: Year = 2025

I also downloaded all the images from the images folder and saved them in a new WOW2025_47 directory I created within my …\My Tableau Repository\Shapes\ directory.

Building the core rank calculation

On a new sheet, add Date to Columns and set to the Month level as a discrete (blue) pill. Add Product Name to Rows and Shipment Qty to Text.

Create a new field

Shipment Rank

RANK_UNIQUE(SUM([Shipment Qty]))

and add to the table. Adjust the table calculation setting, so the field is being computed explicitly by Product Name only.

Create another new field

Rank Display

IF [Shipment Rank] = 1 THEN ‘1st’
ELSEIF [Shipment Rank] =2 THEN ‘2nd’
ELSEIF [Shipment Rank] =3 THEN ‘3rd’
ELSE STR([Shipment Rank]) + ‘th’
END

Build the bar chart

On a new sheet, add Shipment Rank as a discrete (blue) pill to Rows and Shipment Qty to Columns and Product Name to Text.

Add Spiciness Level to Colour and change the field to be an Attribute so the table calc doesn’t break. Then add another instance of Product Name to the Detail shelf, and then click the icon to the left of the pill to add it to the Colour shelf, so there are 2 dimensions on the colour shelf. Adjust the colours to suit.

Tip – the quickest way to get the colours to be in a range is to edit colours > ctrl-click to multi select a set of colours, choose a diverging colour palette and click assign palette.

Edit the axis and reverse the axis. Adjust the size of the bars to make them a bit smaller. Adjust the label and match mark colour. Hide both the axis and the Shipment Rank header and all gridlines, and row/column dividers. Add Date at the Month level to the Pages shelf and update the sheet title.

Creating the Viz in Tooltip

On a new sheet ad Product ID to Rows. Change the mark type to Shape and add Product ID to shape. Edit the shapes to use the WOW2025_47 shapes added. As they are stored in alphabetical ordered, just use Assign Palette to set the right images to the right products. Hide the Product ID header row and remove any row/column dividers. Set the sheet to Entire View.

Back on the bar chart sheet, add Product ID to Detail and set as an Attribute so the table calc doesn’t break. Update the Tooltip to reference the relevant pieces of text and then add a reference to the VIT worksheet

The image should now display on hover of a bar.

Creating the bump chart

On a new sheet, add Date at the month level as a discrete (blue) pill to Columns and Rank Display to Rows. Add Product Name to Detail. Change the mark type to circle.

Add Spiciness Level to Colour and change the field to be an Attribute. Change the icon next to Product Name from Detail to Colour. The colours should automatically change and match the settings made against the bar chart.

Add Date at the month level as a discrete (blue) pill to the Pages shelf. The table will be filtered to January.

On the pages control, check the show history checkbox, then click on the ‘down arrow’ to display the additional history options. Set them as follows : show All marks, show both marks, allow fade and adjust the range, then format the trails to a narrower line at a lower opacity.

As you now press ‘play’ on the pages, the trails will display. HOWEVER (and this took some time to realise), in Desktop, the display of a circle on previous months does not work. Publish to cloud and it does. I did go down some dual axis related route until I discover this 😦

Add Product ID as an attribute to the Detail shelf and Shipment Qty to the Tooltip shelf and update the Tooltip and reference the Viz in Tooltip images sheet as before.

Then tidy up by

  • Removing row dividers
  • Adding column dividers
  • Adding column banding
  • Hide the Date label heading (right click > hide field labels for columns)
  • Hide the Rank Display label heading (right click > hide field labels for rows)
  • Update the title

Then add the sheets onto a dashboard and you should be good to go:-) My published viz is here.

Happy vizzin’!

Donna

Can you create a dynamically zooming map?

Donna ColesLeave a comment

Kyle challenged us this week with another take on zooming into maps, but this time not using spatial parameters.

Modelling the data

There are 4 sheets of data within the provided Excel workbook. The data needs to be related as follows:

  • start with free_bike_status
  • add vehicle_types and relate to free_bike_status on the Vehicle Type Id field
  • add station_info and relate to free_bike_status using a calculated field where1 =1
  • add station_status and relate to station_info on the Station Id field

Building the Core Map

We will be using map layers and spatial functions throughout this challenge. We start by defining the location of each bike and each station

Bike Location

MAKEPOINT([Lat],[Lon])

Station Location

MAKEPOINT([Lat (Station!Info)],[Lon (Station!Info)])

For the bikes, we need to know how much charge it has left

Charge %

SUM([Current Range Meters]) / SUM([Max Range Meters])

format this to % with 0 dp

Add Bike Location to a new sheet. Add Bike Id to Detail and change the mark type to circle. Add Charge % to Colour, and adjust the colour palette as required, and also edit so it is fixed to range from 0 to 1 (ie 0-100%).

Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Click on the 1 null indicator to the bottom left and select filter data to exclude that record from the display.

Select Map > Map Options from the menu and uncheck all the values to prevent the map from bing manually zoomed in/ changed. Then select Map >Background Layers from the menu, and set the Style to dark and click the Streets,Highways etc map option.

Drag Station Location onto the canvas and drop when the Add a Marks Layer option appears. Add Name the Detail shelf and Num Bikes Available to Colour. Change the mark type to square and adjust the colour palette as required and fix to range from 0 to 50.

Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Then move the stations marks card so it is listed below the bikes marks card. This means the bikes are displayed ‘on top’

Identifying the selected bike

Create 3 parameters

pSelectedBike

string parameter defaulted to <empty string>

pLat

float parameter, defaulted to 38.9358 (this is the central point mentioned in the requirements)

pLon

float parameter defaulted to -77.1069 (this is the central point mentioned in the requirements)

Note – originally I planned to just capture the ID of the selected bike then determine the lat & lon of that bike using a FIXED LOD to in turn determine the selected bike’s location, but that really hampered the performance, so I just used the parameter action to capture the required Lat & Lon directly

Show the 3 parameters on the sheet.

Update the 3 entries with a Bike Id and its associated Lat & Lon values (eg Bike Id =
8ec444bc696c2c8837ca0dcad39de819 , Lat = 38.8965 , Lon = -77.0334)

We need to identify the selected bike on the map

Is Selected Bike

[Bike Id]=[pSelectedBike]

Add this to the Size shelf on the bikes marks card. Adjust the sizes so True is listed before False and the sizes are therefore reversed. You may need to adjust the slider on the Size shelf too.

Zooming in to the selected bike

Create a new field

Selected Bike Location

MAKEPOINT([pLat],[pLon])

then create a buffer of 2000m around this (the requirements state 1000m, but I found that there were free bikes that were over 1000m from their nearest station, and if they were clicked on in the grid, the map didn’t display).

Selected Bike Buffer

BUFFER([Selected Bike Location],2000,’m’)

We want the map to ‘zoom’ into this buffer area if a bike has been selected, but show all bikes & stations so we need

Within 2000m

([pSelectedBike]=”) OR ((INTERSECTS([Bike Location],[Selected Bike Buffer])) AND (INTERSECTS([Station Location],[Selected Bike Buffer])))

Add this to the Filter shelf and select True

The map should zoom in, and the bike selected should be quite central to the display (the middle point of the buffer). To verify this, create

Buffer for Zoom

IF [pSelectedBike] <> ”
THEN [Selected Bike Buffer]
END

Add this to the map as another marks layer, and the circular buffer ‘zone’ will be displayed (we’ll keep this here for now for validation purposes).

Reset the pSelectedBike to <empty> and set pLat and pLon back to their default values – the buffer circle disappears.

Kyle hinted that we need to make sure that on ‘zooming out’ the display should be centred on the default values. To ensure this, we want to create a buffer around that central point that encapsulates all the stations and bikes. So we need

Default Location

MAKEPOINT(38.9358,-77.1069)

Default Buffer

BUFFER([Default Location],30,’km’)

Choosing a 30km buffer was just trial and error.

Now update the Buffer for Zoom field to

IF [pSelectedBike] = ” // then we’re in the default ‘show all’ view
THEN [Default Buffer]
ELSE [Selected Bike Buffer]
END

A buffer zone for the whole display is now shown

Ensure the buffer marks card is displayed at the bottom, reduce the opacity of the colour to 0 and remove any border to make the circle disappear. Then click on the eye symbol to the left of the marks card name to make the map layer disabled, so it doesn’t show up on hover.

Finally adjust the Tooltips on the relevant marks cards and then name the sheet Map or similar.

Building the Bike Selector Grid

To build this we will need to identify the closest station to each bike. First we need the distance between each bike and each station

Distance Bike to Station

DISTANCE([Bike Location], [Station Location],’m’)

and then we can create

Distance Bike to Closest Station

{FIXED [Bike Id]:MIN([Distance Bike to Station])}

On a new sheet add Bike Id to Detail and Distance Bike to Closet Station to Colour. Change the mark type to square. Sort the Bike Id by the field Distance Bike to Closet Station ascending.

Add Lat and Lon to the Detail shelf, and update the Tooltip as required. Name the sheet Bike Grid or similar.

Adding the interactivity

Add the two sheets onto a dahsboard, then create 3 dashboard parameter actions

Select Bike

On select of the Bike Grid sheet, set the pSelectedBike parameter with the value from the Bike Id field. When the selection is cleared, reset to <empty string>

Set Bike Lat

On select of the Bike Grid sheet, set the pLat parameter with the value from the Lat field. When the selection is cleared, reset to 38.9358

Set Bike Lon

On select of the Bike Grid sheet, set the pLon parameter with the value from the Lon field. When the selection is cleared, reset to -77.1069

And with that, hopefully the map should zoom in and out as required, albeit a bit slowly… (gif below recorded on Desktop)

My published viz is here.

Happy vizzin’!

Donna

Population Pyramids

Donna ColesLeave a comment

Sean used a new HR dataset from the #RWFD website for this week’s #WOW2025 challenge.

The data provided contained 1 row per person. I started by creating a bin based on the Age field, which I dragged into the dimensions section of the data pane (above the line) after I connected to the data. Right click Age > Create Bins

Age Group

Create bins every 5 ‘units’

Adding this on to Rows you’ll see you get the relevant values

With this field, we can create the other calculations we will need

Total Headcount

{FIXED[Age Group]: COUNT([synthetic_hr_dataset.csv])}

Other Gender

{FIXED [Age Group]:SUM( IIF([Gender]<>’Female’ AND [Gender]<>’Male’,1,0))}

Format both of these to 0 dp

Males

IIF([Gender]=’Male’,1,0)

Format this to 0dp

Females

IIF([Gender]=’Female’,-1,0)

Note, this is -1 due to how we’re going to plot on the chart, but we don’t want the labels displayed with negative numbers, so custom format this field to ,##0;#,##0

Headcount Gap

SUM([Males]) + SUM([Females])

Note – we’re adding since the Females value is actually a negative number

Also custom format this to ,##0;#,##0

Add Total Headcount to Rows and change to be discrete (blue pill). Add Other Gender to Rows too, and again change to discrete.

Add Females to Columns and then add Males to Columns too.

Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to bar. Adjust the Measure Names colour legend.

Make the rows a bit wider. On the All marks card, on the Label shelf, tick show mark labels and align the labels middle centre and bold.

Add Headcount Gap to Columns. Remove the Measure Names field from the Colour shelf on the Headcount Gap marks card.

Create a new field

Colour – Headcount Gap

[Headcount Gap] <0

and add to the Colour shelf on the Headcount Gap marks card and adjust colours accordingly.

I chose to make the tooltip on all the bars to match exactly and reference the information shown. For this I created some other fields

Tooltip – Least Gender

IIF(NOT([Colour – Headcount Gap]),’women’, ‘men’)

Tooltip – Most Gender

IIF([Colour – Headcount Gap],’women’, ‘men’)

On the All marks card, add Males, Females, Headcount Gap, Tooltip – Least Gender and Tooltip – Most Gender to the Tooltip shelf and adjust the tooltip as required

Add a constant 0 reference line that displays a solid black line to both the Female axis and the Headcount Gap axis.

Finally tidy up by

  • Hide the top axis (uncheck show header)
  • Editing the title of the Female axis to read Headcount
  • Remove all gridlines
  • Remove column dividers
  • Update the viz title.

Add the sheet to a dashboard and ta-dah! My published viz is here.

Happy vizzin’!

Donna

Can you build a satellite chart to represent values exceeding 100%

Donna ColesLeave a comment

For this week’s challenge, we’re using the data from a previous challenge and visualising it using Sam Parson’s ‘satellite’ chart idea – see here.

Modelling the data

Connect to the Food Self-Sufficiency csv file then add another connection to the Circle_Scaffold excel file. Relate the two together using a relationship calculation where 1 = 1 (ie relate every row in left hand data source to every row in the right hand).

Since we only care about data from FY2019, add a data source filter to set Fiscal Year = FY2019.

This saves us from having to apply that filter to each sheet we build.

Building a single spiral

On a new sheet, add Prefecture to the Filter shelf and select Akita-ken (which is near the top of the list and has a % value over 200%).

Create a parameter

pMinRadius

integer parameter defaulted to 500

To build the spiral, we need to plot a mark for every percentage point from 0 up to the Food Self-Sufficiency % value. For this we will need to determine an (x,y) coordinate value for each point, which will require some trigonometry, based on the diagram below

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. As we are building a spiral, the radius of the circle will increase as we move around each percentage point, so we need

Radius

[pMinRadius]+[Path Percent Point]

We then need to determine the angle θ. As a circle is 360°, and a complete circle represents 100%, then 1% is 360/100, so the angle (in radians) for each % point plotted round the circle can be calculated as

Angle

RADIANS([Path Percent Point] * (360/100))

X

(SIN([Angle]) * [Radius])/360

Y

(COS([Angle]) * [Radius])/360

Now create the point

Spiral Point

MAKEPOINT([X],[Y])

Note – the X & Y values are divided by 360 due to the spiral we’re building and the increasing radius when displayed using map layers. If we were just plotting X against Y and not using map layers, this wouldn’t be required.

Double click on Spiral Point to automatically add Longitude and Latitude fields to the sheet.

Change the mark type to line and then add Path Percent Point to the Path shelf.

Add Prefecture to the Detail shelf, as it’ll be needed later when we build the trellis and remove the filter.

At this point, the spiral is showing 3 complete revolutions, as the data in the circle_scaffold data set contains info for up to 300%. We need to restrict it so we only show up to the Self-sufficiency ratio… so we need

Filter Percent Point Displayed

[Path Percent Point] <=[Self-sufficiency ratio for food in calorie base 【%】]

Add this to Filter shelf and set to True.

We now want to colour the spiral based on the percentage point associated to each mark plotted being <100%, between 100% & 199% or >= 200%, so we can use

Colour – Spiral

FLOOR([Path Percent Point]/100)

which will return an integer of 0, 1, or 2

Change this field to be discrete and then add it to the colour shelf and adjust colours accordingly.

Now obviously, you might be thinking things aren’t quite right – we’re not starting at the top and rotating differently. Simply pressing the swap rows and columns icon in the menu bar will resolve this, but if we do that too early, we lose the ability to add map layers, so leave as is for now.

Add the label map layer

Create a 0 point

Zero

MAKEPOINT(0,0)

Drag this onto the canvas and drop when the Add a Marks Layer option appears

This has the effect of creating a 2nd marks card

and now we have this, we can press the swap rows and columns icon in the menu bar to get the start of the spiral at X=0

Change the mark type to circle and add Self-sufficiency ratio… and Prefecture to the Label shelf. Adjust the font style and align centrally. Set the colour of the circle to white and increase the size. Move the Zero marks card to be below the Spiral Point marks card.

Rename the marks cards if you wish.

Add the starting point map layer

We need to create a point for the start of each line which is at the 0% mark

Start Point

MAKEPOINT((IF [Path Percent Point] = 0 THEN [X] END), (IF [Path Percent Point] = 0 THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Colour the mark to the same base colour you used for your <100% range and remove the border. Rename the marks card to 3.Start Point

Add the end point map layers

Create a new point to represent the end of each line

End Point

MAKEPOINT((IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [X] END), (IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Add Colour-Spiral to the Colour shelf as a discrete dimension (blue disaggregated pill) and remove the border. Rename the marks card to 4.End Point Outer.

Add another instance of End Point as another marks layer. Again change the mark type to circle and adjust the size so it is smaller than the previous circle, and set the Colour to white. Rename the marks card to 5.End Point Inner.

Tidy up by

  • removing the Tooltip from each layer
  • disabling selection of each map layer (so nothing happens when you hover over it)
  • Hide the axis
  • Remove axis rulers and gridlines, but make sure the zero lines are shown
  • Hide the null indicator

Name the sheet Single Spiral or similar.

Building the trellis

Duplicate the single spiral (so if things go awry, you can get back to this). Then start by adding Prefecture to the Detail sheet of all the marks card.

When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created

Cols

INT((INDEX()-1)%10)

Rows

INT((INDEX()-1)/10)

and make both fields discrete.

Add Cols to Columns and Rows to Rows. Adjust the table calculation setting of each field so it is computing by Prefecture only, and custom sorted descending by Self-sufficiency ratio…

Then show the Prefecture filter and select all values to display the ordered set of Prefectures.

Hide the Rows and Cols fields, remove row & column dividers. Adjust the size of the start and end point circles to suit, and if the zero lines aren’t showing, reduce the size of the label circle map layer and fit to Entire View.

Then name this sheet Trellis or similar and add to the dashboard.

My published viz is here

Happy vizzin’!

Donna