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
{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.
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 PointDisplayed
[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.
I built an initial version of the heatmap only, which I’ve published here, but I couldn’t get the display to match Valerija’s when I clicked on it. (Note – I wasn’t bothered about Viz in Tooltip part at this point). I used 2 dimensions to display the yellow header section, and you can see below, when clicking, this is noticeable. But Valerija’s solution treats the header as a single entity.
I couldn’t figure out how she’d managed this, so I had to have a look at the solution, and then I built my own instance, which is what I’ll now blog about.
Defining the core fields
Connect to the datasource and add a data source filter to restrict the data by the latest year (as I used Superstore 2025, I filtered to 2025). Doing this meant I didn’t have to worry about adding worksheet level filter fields.
On a new sheet add Category and Sub-Category to Rows and add Sales to Text and then sort descending so you can see the data we need to work with. Format Sales to be $ to 0 dp.
Create a new field
Sales by Cat Rank
RANK(SUM([Sales]))
change it to be discrete and then add to Rows. Adjust the setting of the table calculation so it is computing by Sub-Category only, so the ranking restarts for each Category
We will also need to display the Category in upper case, so create
Category Upper
UPPER([Category])
and add to Rows.
Having this tabular layout just lets us clarify how the table calculation will be working.
Building the Heatmap Table
On a new sheet, add Category to Columns and Sub-Category and Sales to Text and align centrally (Note – I originally put Category Upper on the columns, and then changed later after taking all the screen shots)
Add Sales by Cat Rank to Rows. Change it to a continuous (green) pill and edit the axis to be reversed. Adjust the table calculation so it is computing by Sub-Category only.
Create a new field
One
1
Change the mark type to ganttbar and add One to the Size shelf, setting the aggregation to Avg. Increase the size of the mark via the Size shelf to as large as possible. Add Sales to Colour then add a white border.
We need the axis numbers to be central to the ‘row’, so to fix this, double click into the Sales by Cat Rank pill and change it to subtract 0.5 ( [Sales by Cat Rank] -0.5 )
Re-edit the axis to reverse it again.
So our ‘table’ is now displaying data on a axis from 0.5 up to 9.5. To add the header, we’re going to plot another mark at -0.5, so we can display a section of the same size as the existing ones. For this, double click into Rows and type MIN(-0.5).
This has the effect of creating a second marks card
Remove Sales from Colour and Sub-Category from Label. Add Category Upper to Label. Adjust the label text alignment and formatting and change the Colour to yellow.
Make the chart dual axis and synchronise the axis and we now have the required display.
Tidy up by
Remove row & column dividers
Remove gridlines, zero lines & axis ticks
Hide the right hand axis (right click > uncheck show header)
Hide the Category Upper column labels (right click pill > uncheck show header)
Remove the left hand axis title
Fix the left hand axis from -0.5 to 9.5
Format the axis with a custom formatting of #,##0;-#,##0;TOTAL so that 0 is displayed as the word TOTAL (this is very sneaky by the way, and took me a while to work out)
Then format the font to be bold
Adjust the Tooltip on the Sales by Cat Rank marks card to display the Sub-Category and Sales value.
Delete the text from the Tooltip on the MIN(-0.5) marks card
Name the sheet Table or similar
Building the Viz in Tooltip
On a new sheet add Order Date to Columns at the discrete month level (blue pill) and add Sales to Rows. Add Sub-Category and Category to Detail. Format the date axis so the dates are just using First Letter.
Create a new parameter
pSubCat
string parameter defaulted to Bookcases
Then create a field
Is Selected SubCat
[Sub-Category] = [pSubCat]
and add to the Colour shelf. Adjust accordingly, then make sure True is listed first in the legend, so the line is ‘on top’
Create a new field
Label Line
IF [Is Selected SubCat] THEN [Sub-Category] END
and add to the Label shelf and update to allow marks to overlap. Hide the Order Date label heading (right click > hide field labels for columns). Title the chart Line or similar
Remove gridlines and row/column dividers
Go back to the Table sheet, and update the Tooltip of the Sales by Cat Rank marks card to reference the Line chart and update the filter to pass as just <Category>
Adding the final interactivity
Create a dashboard and add the Table sheet. Then add a parameter action
Set Sub Cat Param
On hover of the Table sheet, set the pSubCat parameter with the value from the Sub-Category field, setting the value to <empty string> when the selection is cleared.
If all has been applied, then the line chart should just display the lines associated to the Sub-Categories in the same Category
Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.
Define the parameters
Create a parameter to define the ‘reporting’ date
pBaseDate
date parameter defaulted to 21 Sept 2025
Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against
pWeeks
integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step
Building the KPI card
This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations
MtD Sales
IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END
format to $ with 0 dp.
Prev MtD Sales
IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND [Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END
MtD Sales Diff
SUM([MtD Sales]) – SUM([Prev MtD Sales])
custom format to +”$”#,##0;-“$”#,##0
MtD Sales % Diff
[MtD Sales Diff] / SUM([Prev MtD Sales])
custom format to +0.0%;-0.0%;0%
On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.
Building the Line Chart
Before building the viz, we’ll start by building the calculations and checking them through a tabular display.
On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.
For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.
These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date
However we only want the calculations to be based on the last x weeks, so we want to filter the display.
Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)
But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.
We can do this using a filter based on a table calculation
Filter: Dates for Chart
LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate]) AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]
The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.
Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).
Let’s start to build the viz :
Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.
Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.
Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.
Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).
Add 2 reference lines to the Order Date axis, which reference these pills.
Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm
Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.
To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.
Sales for Base Date
WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))
format this to $ with 0 dp.
25th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)
75th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)
Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).
You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns
With this, we can now work out the ‘text’ we want to disply in the caption
Expected Range Text
IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’ ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’ ELSE ‘within’ END
Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.
Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.
Then edit the caption and remove all text and update, referencing the various fields and parameters.
Building the bar chart
On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field
Diff is +ve
[MtD Sales Diff]>=0
Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the RowAxis Ruler as a thicker grey line.
Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.
My inspiration for this week’s #WOW challenge came from Sarah Palette’s post on X from last year, and has been something I’d been meaning to try for ages, so figured a #WOW challenge was the perfect opportunity. Sarah has her own blog post which contains the core ‘trick’ to nailing the display, but as usual I’m going to walk you through step by step 🙂
After connecting to the data, start by adding Sub-Category to Rows, Sales to Columns, Category to Colour and sort by Sales descending. Adjust the colours to suit.
Format Sales to be $ to 0 dp, and add to Label. Apply a quick table calculation of Percentage of total to the Sales pill, and then format the pill to be % to 1 dp. Add another instance of Sales to Label. Adjust the layout and format the label to 8pt bold and match mark colour. Reduce the Size of the mark.
Double click into the Columns shelf, and manually type MIN(0). Then drag the MIN(0) pill from Columns and drop it on the Sales axis when the green ‘2-column’ icon appears. This automatically adds Measure Names and Measure Values into the view. Re-order the pills in the Measure Values section. This display now has 2 measures sharing a single axis.
Change the mark type to Line and line type to stepped (via the Path pill). Adjust the Label so it is labelling line ends and start of line only. Align label top right. Adjust the size of the line as required.
Click on the MIN(0) pill in the Measure Values section, and while holding down Ctrl drag the pill to Columns to create a copy of the same measure. This is important as typing in another instance of MIN(0) will create another separate measure, and we need it to be the same one.
This will create another marks card (MIN(0)). Remove the Sales pills from the Label shelf, and add Sub-Category instead. Align the label middle right, and add a couple of spaces before the Sub-Category text. Set the chart to be dual axis, synchronise the axis and then remove Measure Names from the All marks card. Adjust the width of each row to push the Sub-Category label into the corner of the L.
To give the labels at the end of the bar some ‘breathing room’, create a field
Max Sales + 10%
WINDOW_MAX(SUM([Sales])) * 1.1
Add this to the Detail shelf on the All marks card. Adjust the table calculation so it is computing by both Category and Sub-Category. Then right click on the bottom axis and Add Reference Line that uses the average of the Max Sales + 10% field for the Entire Table. Don’t display and lines/labels/tooltip
Finally tidy up by
Hiding both axis (right click -> uncheck show header)
Hiding both the Sub Category and Measure Names headers (right click, uncheck show header)
Remove all gridlines, zero lines, row & column dividers
Adjust Tooltip as required
Add a title colouring the text of the words to match the colours used in the legend.
After connecting to the data, add Team to Rows and Timestamp as a continuous (green) pill at the Day level to Columns. Create a new field
Call Count
COUNT([synthetic_call_center_data.csv])
and then add this to Rows.
Add Timestamp to Filter, and select relative date. Set the options to Last 3 weeks, and then additionally check the anchor relative to check box and enter 23 Dec 2024. This is because the data set only goes up to the end of December 2024. Not setting this field will apply the date filter based on ‘today’ so it’s unlikely anything will appear.
(Note – you may also need to update the date properties of the data set to ensure a week starts on a Sunday to get matching numbers: right click the data source and select the date properties option).
To add a marker to the last point, create a field
Call Count – Most Recent
IF LAST()=0 THEN [Call Count] END
Add this to Rows and adjust the table calc setting so it is computing specifically by Day of Timestamp only. By default it was doing this via the Table (across) option, but I tend to always prefer to always explicitly fix what the calculation is computing over, as it won’t then matter where I then move that field too if I choose to change the layout of the viz.
Set the mark type on the Call Count marks card to line, and then adjust the colour to grey and reduce the size. Set the mark type of the Call Count – Most Recent marks card to circle, set the colour to blue and increase the size. Hide the null indicator (right click > hide).
Set the chart to dual axis, synchronise the axis and then remove the Measure Names field from the All marks card.
remove both the axis titles (right click axis > edit axis), hide the right hand axis (right click, untick show header), and format to remove the column divider from the header section only.
Now we’ve got the core display, we need to create the following fields
No. of Calls
WINDOW_SUM([Call Count])
Highest Call Vol
WINDOW_MAX([Call Count])
Lowest Call Vol
WINDOW_MIN([Call Count])
Avg Call Vol
WINDOW_AVG([Call Count])
format this to a number with 0 dp
Calls this period
WINDOW_MAX([Call Count – Most Recent])
the window_max is required here, as the data set we’re displaying at the day level, has 2 values – the latest value and null. We only want to return 1 value, which is the maximum of these.
Previous Period
WINDOW_MAX(IF LAST()=1 THEN [Call Count] END)
LAST()=1 returns the value of the next to last record, and the window_max is again applied, as the nested IF clause will return null for all others records.
Period Var
[Calls this period] – [Previous Period]
Add each of these fields, one by one, to Rows following the steps below
Add to rows (it will automatically display as a green continuous pill).
change to discrete (right click on the pill and select discrete – the pill will turn blue and move to before the green pills)
Explicitly set the table calc to be computing by Timestamp (as above)
Once, you should have something that looks like this
but I noticed, that the display in the solution is sorted based on the total number of calls and not by Team, so add a Sort to the Team pill to sort by Call Count descending
Update the Tooltip if you wish, and then add the viz to a dashboard, floating the Timestamp filter.
Yusuke set this interesting challenge : to combine a ‘bump’/’slope’ chart visualising the change in rank whilst also visually displaying the Sales value for the relevant Sub-Category in the ranked position.
Defining the calculations
This challenge will involve table calculations, so I’m going to start by building out the various calculations that will be required and displaying in a tabular view.
Add Category to Filter and select Office Supplies. Then add Sub-Category and Order Date at the Year level as a discrete (blue) pill to Rows. Add Sales to Text.
Create a new field
Sales Rank
RANK(SUM([Sales]))
And add to the table, and verify the table calculation is set to compute by Sub-Category only.
We will need to ‘colour’ the viz based on the rank compared to the previous year. For this create
Is Min Year
{MIN(YEAR([Order Date]))} = YEAR([Order Date])
which will return true for the first year in the data (in this instance 2022) and then create
Colour
IF [Sales Rank] = LOOKUP([Sales Rank],-1) OR ATTR([Is Min Year]) THEN ‘Same as last year’ ELSE ‘Different from last year’ END
If the rank is the same as the previous one, or it’s the first year, then treat as the same, otherwise treat as different.
Add the Colour field to the table, and this time make sure the table calculation for Colour is computing by Year of Order Date only (while the nested calc for Sales Rank should still be computed by Sub-Category only)
The labels on the viz only want to show in certain scenarios – if it’s the first record (ie for 2022) or there has been a change in rank. We need
Label : Rank & Sub Cat
IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN STR([Sales Rank]) + ‘ | ‘ + MIN([Sub-Category]) END
and
Label : Sales
IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN SUM([Sales]) END
format this to $ with 0dp
Add these to the sheet, and double check the nested table calculations on each pill are computing as required (Sales Rank by Sub-Category only, Colour by Year Order Date only)
Now we have all this, we can start building
Creating the Viz
On a new sheet, add Category to Filter and select Office Supplies. The add Order Date to Columns, but set to be continuous (green) pill at the Year level. Add Sub-Category to Detail and add Sales Rank to Rows as a discrete (blue) pill. Verify the table calculation setting against the Sales Rank pill is by Sub-Category only.
Change the mark type to line and then add Order Date to Path. By default it should be at the Year level as a discrete pill.
This is the ‘bump’ chart.
Now add another instance of Order Date to Columns as a continuous pill at the Year level to essentially duplicate the display. On the 2nd marks card, change the mark type to Gantt
This gives us the ‘starting point’ for each ‘bar’. But we need to determine the size for each bar. First we’re going to ‘normalise’ the sales values for all the sales being displayed so we get a value between 0 and 1, where 0 is the smallest sale, and 1 is the largest.
To see what this is doing, format the field to 2dp, then add the field to the tabular view, and ensure the table calculation is computing by both Sub-Category and Year Order Date.
But the ‘axis’ we want to plot the bar length against is in years, so we need to adjust this size to be a proportion of a year (ie 365 days)
Gantt Size
//proportion of a year [Normalised Sales] * 365
Add this to the Size shelf on the 2nd marks card on the viz. Adjust the table calc setting so it is computing by all the fields listed.
We now have the core concept so now we can start finalising the display.
Make the chart dual axis and synchronise the axis.
Set the view to fit height.
On the 1st marks card (that represents the line)
change the line style to dotted (via the Path shelf)
reduce the Size to suit
change the colour to pale grey
Add Label : Sales and Label : Rank & Sub Cat to the Label shelf.
Adjust the table calc settings of each so the nested table calcs in each have Sales Ranks by Sub Category only and Colour by both the Year Order Date fields only.
Adjust the layout of the text as required
Align the font to be top right
Change the font style (bold & black)
Ensure the Label is set to ‘allow labels to overlap marks’
Remove the Tooltip
On the 2nd marks card, the gantt bar
Add Colour to the Colour shelf and adjust the colours accordingly.
Verify the table calc settings are as expected
I chose to reduce the opacity slightly, so I could see the dotted line underneath (set to 70%)
Add Sales to Tooltip (format to $ with 0 dp) and the adjust Tooltip as required
Then we just ned to finalise the formatting/display
Set the font of the years and rank numbers to black & bold.
hide the Sales Rank label heading (right click > hide field labels for rows)
remove row & column dividers
Add black column gridlines (I set to the 2nd thickness level), and remove any row gridlines
Edit the top axis to have a fixed start (use default option) and end at 31/12/2025 so the 2026 label and line disappears.
Remove the title from the top axis.
Edit the bottom axis – remove the tile, and then set the tick marks to None, so the bottom axis now looks empty.
And that should be it. Now add the sheet to a dashboard and display the category filter as a single select, customising the remove the ‘all’ option.
For this week’s challenge we’re going to look at building an alternative to a stacked bar chart. This challenge was inspired by this post by the Flerlage twins, which in turn was inspired by other members of the #datafam community. Once again, we’re making use of some Premier League data.
Building the viz
Add Team to filter and select Manchester United, Chelsea, Arsenal and Manchester City then add Season to filter and select the last five seasons. Then add Team and Season to Columns and Points to Rows.
Sort team by the field Points descending. Add Team to colour and adjust accordingly. Show Mark labels to display the number of points for each bar.
Add subtotals via the analysis menu > totals > show all subtotals
Hide the subtotal bar by clicking on one of the total bars and selecting hide from the drop down which is defaulted to automatic
Right click on the Total label on the axes and select format – in the left hand side, delete the label field
Create a new calculated field
Total Points
WINDOW_SUM(SUM([Points]))
And add this to rows. Remove Team from the colour shelf on the marks card, and change the colour to pale grey. Increase the size of the bars to be as wide as possible. Uncheck show mark labels. Hide the total bar like we did above.
Make the chart dual axis and synchronise axis. If need be, right click the right axis and move marks to back. Adjust the table calculation on Total Points so it is computing by Season only. Hide the total bar again if it reappears.
We want to label the total bar with the Team and the Total Points so we need to create some more calculated fields
Label: Team
IF [Season] = ‘2021-22’ THEN [Team] ELSE NULL END
We’re being sneaky here, and just labelling the central bar.
Label: Total Points
IF MIN([Season]) = ‘2021-22’ THEN [Total Points] END
Add Label: Team to the label shelf of the Total Points marks card and adjust so it is an attribute – this means it’s not referenced in any table calculations. Add Label: Total Points to the label shelf too.
Adjust the label as required and then change the alignment so the direction of the text is swapped. Format the font as desired.
Delete all the text from the Tooltip on the Total Points marks card and adjust the text on the Points marks card to match the required format.
Hide the right hand axes (right click axis > uncheck show header). Hide the Team column heading. Remove all gridlines/ axis lines/ zero lines and row and column dividers.
Hide the Season label heading (right click on the label > hide field labels for columns).
Format the axes font to be smaller and rotate the axes labels on the Season axis.
Name the sheet Bar chart or similar and add to dashboard.
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.
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!
Donna + DataViz 