Tag: Table Calcs

Can you create a double-sided multi-row Stem-and-Leaf Plot?

Donna ColesLeave a comment

After a couple of weeks off due to holiday, it was my turn to set the challenge. When browsing around for inspiration, I came across this stem & leaf Power BI WOW challenge by Meagan Longoria, which in turn was inspired by a Tableau WOW challenge set by Yusuke in 2024.

So I thought it would be fun to go ‘full circle’ and see if I could recreate Meagan’s challenge in Tableau, which builds on Yusuke’s challenge, as this requires the ‘leaves’ to be spread across multiple rows.

Defining the calculations

The data set provided contains a rows uniquely identified by a Row ID, and each row defines a Species of Iris and the Petal length, which is a decimal number in cm.

To build the stem and leaf chart, we need to first identify the Stem and then the Leaf. If the length is 4.6cm for example, then the stem is 4 and the leaf is 6.

Stem

INT([Petal length (cm)])

Format this to a number with 0 dp and move to the ‘dimensions’ section of the data pane (above the line).

Leaf

INT(ROUND(([Petal length (cm)] – [Stem])*10,0))

Again, format this to a number with 0 dp and move to the ‘dimensions’ section of the data pane (above the line).

Note – Originally my function was INT(([Petal length (cm)] – [Stem])*10), but I found in some occasions this wasn’t given me the right values eg 4.1 was reporting a Leaf of 0, due to the precision of the original number stored. Using the ROUND function to convert the number to have 0 dp resolved this.

Put all these fields into a table like below, so we can start to sense check the other calculations we’ll need.

The final chart will plot the ‘leaves’ as points on an X and Y axis. The central ‘spine’ of the chart is where X=0, and the leaves are the plotted with the position based on the Stem value and then which row and column the leaf is in. Records associated to the Iris-versicolor Species will be plotted on the left side (negatives) while the Iris-virginica Species will be plotted to the right (positives).

The leaves need to organised into rows of 10 per Stem, per Species, sorted by the Leaf value (smallest first). To manage this, we first need to understand how many leaves are associated with each Stem and Species, and give them a ‘counter’ (ie index) per Stem/Species cohort.

Leaf Index per Stem

/*
For each stem per species, index the leaves from 1 to however many there are in the cohort.
*/

INDEX()

Add this into the table, and adjust the table calculation so it is computing by Leaf and Row ID only and add a Custom Sort by Leaf ascending

We now want to identify which ‘row’ (per stem) the leaf will sit on based on it’s index number. If there’s more than 10 leaves per stem, then we need to plot on multiple rows, where the row count starts at 1. Dividing Leaf Index per Stem by 10 will help us do this, but as all leaves indexed from 1-10 need to be on the 1st row, we need to subtract 1 from the index before we divide. We can then convert to a whole number with the INT function, but as we want rows to start at 1, we then need to increment.

Leaf Row Number

INT(([Leaf Index per Stem]-1)/10) + 1

Eg

Leaf Index = 4 -> subtract 1 = 3 -> divide by 10 = 0.3 -> apply INT function = 0 -> add 1 = row 1

Leaf Index = 10 -> subtract 1 = 9 -> divide by 10 = 0.9 -> apply INT function = 0 -> add 1 = row 1

Leaf Index = 11 -> subtract 1 = 10 -> divide by 10 = 1.0 -> apply INT function = 1 -> add 1 = row 2

Add this into the table and apply/verify the table calculation settings are as above.

We then want to identify which column each leaf should be in, which should be a number between 1 and 10 for Iris-virginica and -1 to -10 for Iris-versicolor. We can use the modulo (%10) function for this, based on the Leaf Index per Stem value, to find the remainder if the index is divided by 10. Similarly to before, as all leaves indexed from 1-10 need to be in the equivalent numbered column, we first need to subtract 1 from the index before we find the remainder, but then we need to add 1 to the final result, to get the desired result.

Leaf Column Number

(([Leaf Index per Stem] -1)%10)+1

Eg

Leaf Index = 4 -> subtract 1 = 3 -> %10 =3 -> add 1 = column 4

Leaf Index = 10 -> subtract 1 = 9 -> %10 = 9 -> add 1 = column 10

Leaf Index = 11 -> subtract 1 = 10 -> %10 = 0 -> add 1 = column 1


Add this into the table and apply/verify the table calculation settings are as above.

Now we know the Stem and the leaf row and column position, we can define the actual X and Y points for each leaf.

X Axis

FLOAT(IIF(MIN([Species])=’Iris-versicolor’, -1 * [Leaf Column Number], [Leaf Column Number]))

This is essentially just taking the Leaf Column Number and making it negative for the Iris-versicolor Species. We’ve wrapped it in a FLOAT to make the number decimal, as we need the axis to be able to handle decimal values later on.

For the Y Axis, we’re going to plot the leaves on rows at intervals of 0.2 related to the stem position

Y Axis

MIN([Stem]) + (0.2 * [Leaf Row Number])

Add these into the table and apply/verify the table calculation settings are as above.

Building the viz

Now we have the core data we need, we can start to build the viz

On a new sheet, add Species, Row ID, Stem and Leaf to Detail. Then add X Axis to Columns and Y Axis to Rows. Adjust the tableau calculation settings as before (remembering to apply the custom sort too!)

Change the mark type to circle. Add Species to Colour and adjust as required, adding a coloured border. Set the sheet to Entire View. Increase the Size a bit, then move Leaf from Detail to Text. Align middle centre and bold and allow labels to overlap. Reverse the Y Axis.

Fix the Y-Axis from 2.5 to 7 and set the tick marks to occur at intervals of 1.

Format Petal length (cm) to be a number with 1 dp, then add to Tooltip and adjust accordingly.

Set the background of the worksheet to pale blue. Remove column gridlines. Set the row gridlines to be a pale blue. Remove zero lines, axis rules & tick marks.

To plot the Stem value, double click into the Columns and manually type MIN(0.0) to create a second axis. Remove all the fields except Stem from the marks card of this axis. Change the mark type to shape and use a transparent shape. Move Stem onto Text and align middle centre and increase font size and make it bold. Clear all the text from the Tooltip associated to this mark.

Make the chart dual axis and synchronise the axis. Then add a Reference Band to the X-Axis which plots at -0.5 to 0.5, formatted with a blue line and a pale yellow fill.

Finally, hide all the axes (uncheck show header) and remove row & column dividers.

Add to a dashboard, using containers to organise the content. Use a horizontal container above the main viz to add text fields to label the parts of the chart. Ensure the chart specific title, the label headings and the chart itself are in a vertical container which can then have formatting applied (border/ curved edges etc). My dashboard item hierarchy is shown below

My published viz is here.

Happy vizzin’!

Donna

Mekko charts & Offset Labels

Donna ColesLeave a comment

This week’s WOW challenge for community month, saw Sean partner with Tableau Visionary and ‘Hall of Famer’, Linsdsay Betzendahl to deliver this double challenge – creating a Mekko chart with staggered labels AND a stacked bar chart with staggered labels.

There was quite a lot going on in this challenge, and I had utilised all the concepts in the past, but did still need a refresher to remind how to get things done. So I did draw on the links provided in the challenge, as well as blogs I’ve written for other WOW challenges, specifically

Modelling the data

The link in the challenge downloaded a CSV file to connect to. Once connected, the instruction was to pivot the data, so all the ‘types of death’ columns were transposed from rows into columns. In the data source pane, multi-select all the ‘Total Dead xxxx’ columns then right-click and select pivot

I then renamed the Pivot Field Names column as Disaster Type Raw and Pivot Values as Decadal Deaths.

In the Data pane, I then created a group against the Disaster Type Raw field, called Disaster Type. Every value was grouped, even if it only contained a single entry, just so I could display the name of the disaster in the way that matched the solution

Building the calcs for the Mekko chart

A Mekko chart is based on % – in this instance, the height of the chart is split based on the % of deaths by type per decade, while the width is based on the % of total deaths per decade. For this we’ll need some table calculations, so building out a tabular view to check the values is always something I find very valuable.

Change the Year field to be discrete, then add to Columns. Add Disaster Type to Rows and Entity to Filter and restrict to World. Add Decadal Deaths to Text.

To get the % of deaths by type per year, create

% Decadal Deaths

SUM([Decadal Deaths]) / TOTAL(SUM([Decadal Deaths]))

Add this into the table and then adjust the table calculation so it is computing by Disaster Type only, which essentially means based on the layout of this table view, that each column should be adding up to 100%

This is giving us the value we need to plot on the Y-Axis of the chart (ie the height)

To determine the value to plot on the X-Axis, we need several calculations. First we need to know the total deaths per decade

Total Deaths per Decade

{FIXED [Year],[Entity]:SUM([Decadal Deaths])}

Add this into the table. The value should be the same for every row for the same year.

Then we need to know what this value is as a % of all the deaths across all the decades

% Total Deaths per Decade

SUM([Total Deaths per Decade]) / TOTAL(SUM([Total Deaths per Decade]))

Add this into the table, and adjust the table calculation so that it is computing by Year, which means the values per row should add up to 100%

To build a Marimekko chart, which is a variable width chart, we need to use the cumulative value of this %, so we plot points from the start up to 100%. So we need

Running Sum % Total Deaths per Decade

RUNNING_SUM([% Total Deaths Per Decade])

Add this into the table, verify the table calc is computing explicitly by Year as described above for both the nested calculations, and we should see the values increasing up to 100%

Now typically this would be enough for the chart and is what is mentioned in the referenced blog posts for building these charts. However, due to the staggered labelling requirement, we need to go a step further, as the value we plot on the X-Axis needs to be used for both the Mekko chart and the ‘pointers’ associated to the staggered labels. As these labels are centred, we need to plot our point midway

Mekko – Plot Point X Axis

[Running Sum % Total Deaths Per Decade] – ([% Total Deaths Per Decade]/2)

ie take the running sum value, but subtract half of the % value for the decade.

As before add to the table, setting all nested table calculations to be computing by Year.

Now we have all this, we can start to build out the chart

Building the Mekko chart

On a new sheet, add Entity to Filter and restrict to World. Add Year to Detail and Disaster Type to Colour, and adjust colours to suit.

Then add Mekko – Plot Point X-Axis to Columns. Adjust the table calc, so both next calculations are computing by Year only. Then add % Decadal Deaths to Rows and adjust the table calc to be computing by Disaster Type only. Change the mark type to bar and adjust the sort on the Disaster Type field to sort by field Decadal Deaths ascending

Then add % Total Deaths per Decade to Size. Adjust the table calc so it is computing by Year only. Then click on Size and change the option from Manual to Fixed and align Centre, so the width of the bar is stretched equally, either side of the central plot point. Add a white border via the Colour shelf.

Add Decadal Deaths and Total Deaths per Decade to Tooltip and update as required.

Add Disaster Type to Label, then adjust the Label settings so the label is only displayed based on the maximum value of the Year field, and match the mark colour.

At this point, while the labels are displayed, they aren’t in the required location. To resolve this, start by extending the X axis to be fixed to end at 1.2 (right click axis > Edit)

This extends the axis, giving some ‘space’ in the chart for the labels. Trying to adjust the alignment of the labels doesn’t work though, so move them manually. Click on the label so the cursor changes to a cross, and then drag the label to the side to reposition it.

Do this for all the labels displayed in the solution, doing your best to align them vertically. Once done, then in the label settings, uncheck the ‘allow labels to overlap other marks’ option, so the other values disappear.

Now we have the Mekko chart, we now need to handle the staggered labelling.

Mekko – Label Position YAxis

IF INDEX()%2 = 1 THEN -0.1 ELSE -0.3 END

We’ll index the year from 1 to 13, and every odd number index will have the label positioned at -0.1 and even at -0.3

Add this to Rows which will create a 2nd marks card. Remove all the fields except Year and Total Deaths per Decade from the card.

Adjust the table calc to explicitly compute by Year, and then change the Size from Fixed to Manual and reduce the slider to as small as possible. You should now have your label ‘pointers’

Move the Year and Total Deaths per Decade pills onto the Label shelf, then set the Label settings, so All labels are shown, they can overlap other marks, and adjust the alignment, so they’re not skewed (you may need to find you have to click the sideways position first before you can get the alignment working)

Adjust the formatting of the label, then edit the Mekko – Label Position Y Axis axis so that it is fixed to start at -0.5, and the title and tick marks are removed, so the axis looks to disappear.

Finally tidy up by removing all gridlines, row and column dividers and hiding the Mekko – Plot Point X Axis axis.

Add to a dashboard, and include the requested citation to the data source. I added this within a floating text box with a show/hide button.

Building the calcs for the Bar chart

The bar chart employs similar principals as the Mekko chart, we need to calculate a position to plot the point on the X-axis, but the bar has a fixed height and not split. Let’s start with getting the calcs we need again.

Build a table with Entity on Filter (filtered to World), Year on Rows and Disaster Type on Columns. Add Decadel Deaths onto Text and then add % Decadel Deaths into the table, and adjust the table calc so it is computing by Disaster Type. Due to the layout of this table, the values in each row now add up to 100%.

The % Decadal Deaths defines the ‘width’ of each segment in the bar chart, so one again we need a cumulative value to help determine the plot point.

Running Sum % Decadal Deaths

RUNNING_SUM([% Decadal Deaths])

Add to the table and ensure all the table calcs for the nested calcs are all computing by Disaster Type.

And as before, as we need a ‘central’ point to plot, create

Bar – Plot Point X Axis

[Running Sum % Decadal Deaths] – ([% Decadal Deaths]/2)

Add to the table and ensure all the table calcs for the nested calcs are all computing by Disaster Type.

There is an additional requirement for this chart, to restrict what is displayed based on the Year selected. Create a parameter based off of the Year field

pYear

integer parameter, defaulted to 2020 (formatted without thousand separators), that lists the values

Then create a field

Show Last 3 Decades

[Year]>= [pYear]-20 AND [Year]<=[pYear]

and add to the Filter shelf and set to True.

For the label pointer position, we also need a similar field as before, but only want labels to show when the % is over 4%, so create

Bar – Label Position Y Axis

IF [% Decadal Deaths] > 0.04 THEN
IF INDEX()%2 = 1 THEN -0.1 ELSE -0.3 END
ELSE NULL
END

format to show 2dp, then add to the table, and ensure all table calcs are computing by Disaster Type.

Values will only display if the % Decadal Deaths value is >4%, and the value alternates based on the index position.

Building the bar chart

The steps are very similar to the Mekko chart, except this time, we need the Year dimension on Rows and we can ‘hard code’ a ‘y-axis’ by double clicking into Rows and typing MIN(0.5). This axis is then fixed from 0 to 1 to provide some spacing. Bar – Plot Point X Axis is on Columns and % Decadal Deaths is on Size with the size once again Fixed and centred. This time to Disaster Type field is sorted by Decadal Deaths descending.

Add Bar – Label Position Y Axis to Rows to create the 2nd marks card, and again apply similar steps as above to end up with labelled pointers. I set the axis of this measure to be fixed from -1

The you just need to tidy up the formatting by removing the gridlines, axis lines, row/column dividers and hiding the various axis. Then add to a dashboard and show the parameter.

My published vizzes are here:

Happy vizzin’!

Donna

Can you build a parallel coordinates chart?

Donna ColesLeave a comment

This week’s challenge uses data I collated from the stats vest my daughter wears during her football matches and training sessions. The data provided is already pre-filtered to the 2024-25 season and her Reading U15 team.

If you want to watch a video which demonstrates the concept then this video by Andy Kriebel will help. But if you prefer to read, then carry on 🙂

Building out the calculations

In creating this chart we need to normalise all the different stats we want to show; that is we want to convert every measure into a spread between 0 and 1. To do this we calculate the difference between the value and the minimum value as a proportion of the full range of values (that is the difference between the min and max values). We use table calculations for this.

Total Distance (km)

(SUM([Distance – Total (KM)]) – WINDOW_MIN(SUM([Distance – Total (KM)])))
/
(WINDOW_MAX(SUM([Distance – Total (KM)])) – WINDOW_MIN(SUM([Distance – Total (KM)])) )

format to 4 dp

Distance / Min (m)

(SUM([Distance per Min]) – WINDOW_MIN(SUM([Distance per Min])))
/
(WINDOW_MAX(SUM([Distance per Min])) – WINDOW_MIN(SUM([Distance per Min])) )

format to 4 dp

HSR Distance (m)

(SUM([HSR – Total (M)]) – WINDOW_MIN(SUM([HSR – Total (M)])))
/
(WINDOW_MAX(SUM([HSR – Total (M)])) – WINDOW_MIN(SUM([HSR – Total (M)])) )

format to 4 dp

HI Distance (m)

(SUM([HI Distance – Total (M)]) – WINDOW_MIN(SUM([HI Distance – Total (M)])))
/
(WINDOW_MAX(SUM([HI Distance – Total (M)])) – WINDOW_MIN(SUM([HI Distance – Total (M)])) )

format to 4 dp

Max Speed (m/s)

(SUM([Speed – Max (m/s)]) – WINDOW_MIN(SUM([Speed – Max (m/s)])))
/
(WINDOW_MAX(SUM([Speed – Max (m/s)])) – WINDOW_MIN(SUM([Speed – Max (m/s)])) )

format to 4 dp

NOTE – in my solution I called these fields Norm – xxxx . After putting on the chart, I aliased them to the names above. Creating with the actual names I used is the simpler solution 🙂 I don’t know why I didn’t just rename them…

On a new sheet, add Game ID to Rows, and then add all the original key measures and their associated normalised values into the table, so each original measure is next to its normalised value. If you sort by one of the original measures from largest to smallest, you should see that the associated normalised value is distributed from 1 at the top of the list to 0 at the bottom.

The other key requirement for this viz is to identify a selected opposition. For this we create a parameter based on the Opposition field. Right click on Opposition > Create > Parameter and the following dialog with the list of opposition teams pre-populated will display. I set the default as Chelsea Ladies U14

Opposition Parameter

The create a new field

Highlight Match

[Opposition] = [Opposition Parameter]

Show the Opposition Parameter control and add Highlight Match to Rows.

Now we can start building the viz.

Building the chart

On a new sheet, add Game ID to Detail, then add Total Distance (km) to Rows. Adjust the table calculation of the field to be computing by Game ID.

Then add Distance / Min (m) onto the sheet, by dragging the field and dropping it on the y-axis when the 2-column icon appears

This will automatically add Measure Names and Measure Values into the display, including a Measure Values ‘box’

Adjust the table calc of the Distance / Min (m) field to compute by Game ID. Reorder the fields in the Measure Values box, and then change the mark type to line. Set to Fit Width.

Add the other 3 normalised fields into the view, by adding the field into the Measure Values box, and adjusting the table calc each time.

We now have the core viz, we just need to format it and add the additional features.

Show the Opposition Parameter control. Add Highlight Match to the Colour shelf. Adjust colours and re-order so True is listed first. Adjust all the table calcs in the 5 measures so they are also now computing by both Game ID and Highlight Match.

Now add H/A to the Detail shelf and change to be an Attribute (this means by default it is excluded from the table calcs, so we don’t need to make all those adjustments again). Then change the icon to the left of the pill from Detail to Colour, so there are now two fields on the Colour shelf. Adjust to suit.

Add Highlight Match to the Size shelf, and then adjust the size to be reversed, so the True lines are thicker.

Create a new field

Label: Opposition

IF [Highlight Match] THEN [Opposition] END

And add this to the Label shelf and set to be an Attribute. Adjust the Label property so it’s just labelling the line ends and label end of line. Ensure labels set to overlap to, and set font to bold and to match mark colour and align top right.

Alias the field H/A (right click field > aliases) so they display as Home and Away. Then add the following fields to the Tooltip : Category, Cup/League, H/A, Opposition, Round and Date. Also add the following measures to the Tooltip : Distance – Total (KM), Distance per Min, HI Distance – Total (M), HSR – Total (M), Speed – Max (m/s). Then adjust and format the Tooltip

To create the vertical lines, add another copy of Measure Values to the Rows which will create a Measure Values (2) marks card. Remove all the fields from this card except the Game ID. And then move Game ID to the Path shelf which has the effect of creating the vertical lines

Adjust the Colour of the line and then add Measure Names to the Label shelf. Set the label to Min/Max by pane, for the Measure Values field. Align centrally.

Set the chart to dual axis and synchronise the axis. Hide all the axis, and the Measure Names labels at the bottom (uncheck show header against the pills). Remove all row/column dividers and gridlines/zero lines etc. Set the background colour (if desired).

Adding the interactivity

Add the chart to a dashboard then create a dashboard parameter action

Set Opposition

On select of the viz, set the Opposition Parameter parameter, with the value from the Opposition field. Set the value to <empty> when cleared.

Adding the information panel

Add a floating Text object onto the dashboard. Copy and paste the information from the challenge page. Resize and reposition the text box as required. Set the background of the text object and add a border to make it prominent. Then from the context menu of the object, select the Add Show/Hide Button option. The Show/Hide object will appear as an X. Move this into the required position and re-adjust the text object to suit too.

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

Happy vizzin’!

Donna

Exploring Table Calcs vs LODs

Donna ColesLeave a comment

Erica set the challenge this week, which involved building the same visualisation in two ways – one using Table Calcs only and one using LODs.

To test the functionality and validate what’s going, I’m going to build the data out in a tabular format first.

The Table Calc Table

On a new sheet add Region and Category to Rows and Sales to Text. Sort by Sales descending. Then add Region and Segment to Filter, selecting all options, and show the filters.

Create a new field

Total Sales

TOTAL(SUM([Sales]))

and add into the table. This won’t be used later, but I’ve created so you can see the filtering behaviour.

This is a table calculation, and, by default when added to the table, it shows the total of all the rows against each row. We want to display the total per Region, so every row for a specific region shows the total of that region only. Adjust the table calc setting so it is computing by Category only.

Create a new field

TC – % of Sales

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

format to be % with 1dp and add into the table. By default it should have inherited the table calc settings we just applied for Total Sales, but if not apply the same settings, so it is computing by Category only.

If you do the maths taking the value in the Sales column and dividing by the value in the Total Sales column, you should be able to validate the result.

And if you test by adjusting the Segment and Region filters, everything should work as expected :

Filter out a Segment and the Sales and Total Sales adjust to exclude the value and the TC – % of Sales for a Region still totals 100%

Filter out a Region and that whole block is removed, but the values for all the other rows remain unchanged.

Now add Category to Filter and show the filter. Filter out a Category, and what we now see is the Total Sales now just reflects the total for the selected categories, and the TC – % of Sales still totals 100%. This isn’t the requirement. The % of Sales needs to calculate over all Categories regardless if it is excluded in the display.

To handle this, we create

TC – Filter Category

LOOKUP(MIN([Category]),0)

This is using a table calculation to basically return the value of the Category associated to the current row, which is basically looking up itself. However, as this is a table calculation, when added to the Filter shelf, it applies the filter after other computations (this is Tableau’s order of operations’), whereas the ‘quick filter’ applied above, is filtering the data first before then computing the total sales.

Remove Category from the Filter shelf, and add TC – Filter Category to the Filter shelf instead, selecting all options and showing the filter on the view. By default the table calc setting is set to table down which is fine – it is computing by both Region and Category. Now if you exclude a Category, the relevant rows disappear, but the Total Sales and therefore TC – % of Sales values remain unchanged.

The Table Calc Viz

The simplest way to build this (IMO) is to duplicate the table sheet then

  • Remove Total Sales and Sales
  • Move TC – % of Sales to Columns
  • Show mark labels
  • Adjust colour of bar
  • Adjust colour of worksheet background
  • Widen each row slightly
  • Make the width of bars slightly smaller
  • Edit title of % of Sales axis
  • Adjust fonts of label headers and bar label and axis labels (I set to 8pt)

The LOD Table

Let;s repeat this now using LODs instead.

On a new sheet, once again add Region and Category to Rows and Sales to Text. Add Region and Segment to Filter and show the filter options.

Create a field

LOD – Sales per Region and Segment

{FIXED [Region], [Segment]:SUM([Sales])}

Add into the table. With all segments selected, we can see the total sales for each Region listed

and filtering out a Segment, the totals adjust as required.

If we now add Category to Filter, show the options and filter one out, the totals now no longer change, as the LOD – Sales per Region and Segment doesn’t include Category in its definition – its FIXED to just account for changes to Region and Segment.

So we can now create

LOD – % of Sales

SUM([Sales]) / SUM([LOD – Sales per Region & Segment])

and format to % with 1dp and add into table to validate.

You can now apply similar steps to those detailed above to recreate the bar chart viz for this data.

And once done, add both charts onto a dashboard with their relevant filters.

My published viz is here.

Happy vizzin’!

Donna

Can you build a ranked heatmap tile?

Donna ColesLeave a comment

Erica had a guest coach, Valerija Kirjackaja setting the challenge this week, asking us to use table calculations to build this heatmap table.

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 gantt bar 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

My published viz is here.

Happy vizzin’!

Donna

Let’s make a Tableau Pulse-Inspired Dashboard!

Donna ColesLeave a comment

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.

25th Percentile

MODEL_QUANTILE(“model=gp”,0.25,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

and we also need the 75th percentile

75th Percentile

MODEL_QUANTILE(“model=gp”,0.75,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

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.

Dates to Include

[Order Date]>= DATEADD(‘week’, -1 * [pWeeks], [pBaseDate]) AND [Order Date] <= [pBaseDate]

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.

Create new fields

Ref Line – Start of Month

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

and

Ref Line – End of Month

DATE(DATEADD(‘day’,-1, DATEADD(‘month’, 1,DATETRUNC(‘month’, [pBaseDate]))))

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 Row Axis 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.

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

Happy vizzin’!

Donna

Let’s practice Table Calcs!

Donna ColesLeave a comment

For this week’s #WOW2025 challenge, Sean went ‘back to basics’ with a focus on table calculations.

Let’s jump right in.

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.

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 visualise yearly rank change in Sub-Category sales?

Donna ColesLeave a comment

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.

Normalised Sales

((SUM([Sales]) – WINDOW_MIN(SUM([Sales]))) / (WINDOW_MAX(SUM([Sales])) – WINDOW_MIN(SUM([Sales]))))

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.

My published viz is here

Happy vizzin’!

Donna

Let’s Practice RegEx in Tableau with Generative AI

Donna ColesLeave a comment

Yoshi set this week’s challenge which focused on 2 areas : the use of tools such as CHAT GPT to help us create Tableau RegEx functions (which are always tricksy), and the build of the chart itself.

Creating the REGEX calculated fields

I chose to use CHATGPT and simply entered a prompt as

“give me a regex expression to use in Tableau which extracts a string from another string in a set format. The string to interrogate is”

and then I pasted in the string I’d copied from one of the Session Html fields. I got the following response

along with many more code snippet examples. I used these to create

Session Title

REGEXP_EXTRACT([Session Html], '<div class="title-text">(.*?)</div>')

Description

REGEXP_EXTRACT([Session Html], '<div class="description"[^>]*><div>(.*?)</div>')

Location

REGEXP_EXTRACT([Session Html], '<span class="session-location"[^>]*>(.*?)</span>')

Session Level

REGEXP_EXTRACT([Session Html], 'session-level-([a-zA-Z]+)')

Alias this field to show the values in proper case (ie ‘Advanced’ rather than ‘advanced’) and display Null as ‘All Levels’

Session Date

REGEXP_EXTRACT([Session Html], '<span class="semibold session-date">(.*?)</span>')

Session Time

REGEXP_EXTRACT([Session Html], '<span class="semibold session-time">(.*?)</span>')

Start Time

REGEXP_EXTRACT([Session Html], '<span class="semibold session-time">([0-9: ]+[AP]M)')

End Time

REGEXP_EXTRACT([Session Html], '- ([0-9: ]+[AP]M)')

Creating the additional fields

All of the RegEx functions return string fields. To build the viz, we need actual date fields and additional information

AM|PM

IIF(CONTAINS([Start Time],’AM’), ‘AM’, ‘PM’)

Date

DATE(DATEPARSE(“MMMM d yyyy”, SPLIT([Session Date], “, “, 2) + ” 2023″))

returns the day of the session as a proper date field, all hardcoded to 2023, since this is when the data is from.

Start Date

DATETIME(STR([Date]) + ” ” + [Start Time])

returns the actual day & start time as a proper datetime field.

End Date

DATETIME(STR([Date]) + ” ” + [End Time])

Duration

DATEDIFF(‘second’, [Start Date], [End Date])

Add fields to a table as below

Firstly, when we build the viz, we’ll need to sort it based on the Start Date, the Session Level (where Advanced is listed first, and All Levels last) and the Duration (where longer sessions take higher precedence) . For this we need a new field which is a combination of information related to all these values.

Sort

STR([Start Date]) + ‘ – ‘ + STR(
CASE [Session Level]
WHEN ‘advanced’ THEN 4
WHEN ‘intermediate’ THEN 3
WHEN ‘beginner’ THEN 2
ELSE 1
END
) + STR([Duration]/100000)

(this just took some trial and error)

Add this to the table after the Session ID pill and then apply a Sort to the Session ID pill so it is sorting by the Minimum value of the field Sort Ascending. You should see that when the start time and session level match, the sessions are sorted with the higher duration first.

The viz also lists the AM & PM sessions as separate instances, where the 1st session of the PM session is displayed on the same ‘row’ as the 1st session of the AM session. To handle this we need

Session Index

INDEX()

Make this discrete and add to the table after the AM|PM pill. Change the table calculation so it is computing by all fields except AM|PM and Session Date. The numbering should restart at each am/pm timeslot

Finally, the last field we need to build this viz, is another date field, so we can position the gantt chart bars in the right places. As the viz already segments the chart by Session Date and AM|PM, we can’t reuse the existing Start Date field as this will display the information as if staggered across the 3 days. Instead we want to create a date field, where the day is the same for al the sessions, but just the time differs

Baseline Date

DATETIME(STR(#2023-01-01#) + ” ” + [Start Time])

the date 01 Jan 2023 is arbitrary and could be set to any day. Add this into the table, so you can see what it’s doing. You will probably need to adjust the table calculation on Session Index again to include Baseline Date in the list of checked fields.

Building the viz

On a new sheet, add Session Date to Columns and manually re-order so Tuesday listed first. Then add AM|PM to Columns . Add Baseline Date as a continuous exact date (green pill) to Columns . Add Session Id to Detail then add Session Index to Rows and adjust the table calculation so it is computing by all fields except Session Date and AM|PM.

Create a new field

Size

SUM([Duration])/86400

and add this to the Size shelf. 86400 is the number of seconds in 24hrs, so adjusts the size of the gantt bars to fit the timeframe we’re displaying.

Add Session Level to Colour and adjust accordingly. You will need to readjust the Session Index table calc to include Session Level in the checked fields. Add a border to the bars. Edit the Baseline Date axis so the axis ranges are independent

Add Description and Session Title to Detail and again adjust the Session Index table calc to include these fields too. Apply a Sort to the Session Id field so the data is sorted by the Sort field descending

Add Location, Start Time and End Time to Tooltip, and update accordingly. Then apply formatting to

  • change font of the Session Date and AM|PM header values
  • remove the Session Date/ AM|PM column heading label (right click > hide field labels for columns)
  • Hide the Session Index field
  • Hide the Baseline Date axis
  • Add column banding so the Wednesday pane is coloured differently

Add the ability to highlight the Session Title and Description by selecting Show Highlighter from the context menu of each pill

Then add the sheet to a dashboard, using containers to display the colour legend and the highlighter fields.

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

Happy vizzin’!

Donna