Let’s Viz Tsukurima Show!

Donna ColesLeave a comment

This week’s #WOW2026 challenge was inspired by the annual viz challenge held at the Japanese TUG. It took me a bit longer than 10 minutes to build though!

I’ve built this using 3 sheets – a sheet for the section of the calendar above the displayed bar chart, the bar chart and then the section of the calendar below the bar chart.

Building the Calendar Sheets

On a new sheet, add Order Date at the month-year level to Filter and select December 2025. Then add Order Date at the Weekday level to Columns and Order Date at the Week (number) level to Rows. Additionally add Order Date at the month-year level to Columns as a discrete (blue) pill positioned first.

Create a parameter

pSelectedDate

date parameter defaulted to 10 Dec 2025

and show this parameter on the view.

Create a new field

Is Selected Date

[pSelectedDate] = [Order Date]

Change the mark type to circle then add Is Selected Date to colour. Adjust colours to suit and add a dark border to the circle. Set the sheet to entire view. Add Order Date at the Day level to Label and align middle centre.

We want the calendar to show ‘no selected date’, so will utilise 01 Jan 1900 as a ‘null’ date. We also want the calendar to only show the dates related to the week of the day selected and any previous in the month. So create

Show Top

[pSelectedDate] = #1900-01-01# OR WEEK([Order Date])<= WEEK([pSelectedDate])

and add to the Filter shelf and set to True

We also need all the days of the week to display, even if there aren’t sales on those dates. Eg, set pSelectedDate to 03/12/2025 and we lose the ‘Sunday’ column.

To resolve this, select the Show Empty Columns from the Analysis > Table Layout menu

Adjust Tooltip to suit.

Hide the Order Date column heading (right click > hide field labels for columns). Adjust the format (font size) of the other labels. Hide the WEEK(Order Date) row heading (right click, uncheck Show Header). Hide row & column dividers.

Create fields

True

TRUE

False

FALSE

and

Date for Param

IF [Order Date] = [pSelectedDate] THEN #1900-01-01# ELSE [Order Date] END

Add all these to the Detail shelf, setting the Date for Param field to be a discrete (blue) exact date.. They’ll be needed for the interactivity later.

Name this sheet Top or similar, then duplicate the sheet and change the Show Top filter to be False. Then hide the header labels (uncheck Show Header). Remove the True & False fields form the Detail shelf. Name this sheet Bottom or similar.

Building the bar chart

On a new sheet add Is Selected Date to the Filter shelf and set to True. Add Sales to Columns and Region to Colour and add Region to Label. Expand the width to make the bar wider.

Add Order Date to Tooltip and adjust Tooltip to suit. Set the background colour of the worksheet to dark grey, then format axis to have white font. Remove gridlines.

Building the dashboard

Use a vertical container, add the Top sheet and then then Bottom sheet underneath. Remove all padding from these sheets, and remove the title

Then add a horizontal container between the 2 calendar sheets. Add a text object and adjust text as required and reference the pSelectedDate parameter. Then add an image object, and select the image you hopefully downloaded from the link in the challenge page. Then add the bar chart, removing the title and setting to fit entire view.

Remove all padding for each object in this container and set the background colour of the text and image objects to the same dark grey as the bar chart.

Change the colour of the font in the text box, and I set some inner padding on the text object and the bar chart (10 pts all round) just for some extra breathing room. If need be, set the background colour of the bar chart object too.

Change the date in the pSelectedDate parameter and watch the bar chart move

Adding the interactivity

Add a dashboard parameter action

Select Date

on select of the Top or Btm sheets, set the pSelectedDate parameter by passing the value from the Date For Param field.

Also add a dashboard filter action

Unhighlight Top

On select of the Top sheet on the dashboard, target the Top sheet directly, explicitly setting the True field to False. Show all values when selection cleared.

Finally, we need to make the horizontal container disappear when no date is selected (ie the pSelectedDate parameter is set to 01 Jan 1900).

Create a new field in the data set

Is Not Null Date

[pSelectedDate] <> #1900-01-01#

then back on the dashboard, select the whole horizontal container, and from the Layout pane, check the option to control visibility using value, and select the Is Not Null Date field

When the date is 01 Jan 1900, this whole section will now disappear. You should be able to click dates now, and get the functionality as expected.

Once done, you can tidy up the dashboard by removing the container with all the filters and legends, adding a title, and adding some extra padding and background colours as required.

And that should be “Tabutta!”

My published viz is here.

Happy vizzin!

Donna

Can you create a Gauge Chart with Tableau Extensions?

Donna ColesLeave a comment

For this week’s challenge, Yoshi got us creating gauge charts using Tableau’s radial viz extension.

Modelling the data

Yoshi provided a version of the Superstore dataset along with a Budget csv. After downloading, I related the 2 files within Desktop using the following relationships pictured below

Building the gauge chart

On a sheet, select the Add an extension option from the mark type dropdown and then select the Radial (by Tableau) option, and click Open on the resulting screen

Add Measure Names to Ring and Measure Values to Angle and then add Measure Names to filter and retain the Sales and Budget options only.

Click Format Extension and then set the options as follows

  • Total angle (degrees) : 180
  • Starting angle (degrees) : 270
  • Ring padding : 30
  • Segment padding : 5
  • Segment labels : Ring and Angle Values
  • Font : size 9
  • Centre size (%) : 50
  • Show centre label: on
  • Automatic font size: on
  • Edit colours and select appropriate colours

Adjust the order of the Sales and Budget pills in the Measure values pane if required

Add Region, Segment, Category and Order Date at the month-year level to the Filter shelf. Set Category to Furniture and Order Date to December 2025.

Format the Measure Values pill on the Angle shelf so that it displays the numbers as $ with 0 dp.

Create a new field

Acheivement %

(SUM([Sales])/SUM([Budget])) / 2

format this to % with 1 dp and add to the Centre shelf. Note, the division by 2 is necessary due to their being 2 measures (and therefore 2 marks) displayed and the number duplicating itself.

Create another field

Tooltip – Achievement %

(SUM([Sales])/SUM([Budget]))

Format this to % with 1 dp and add to the Tooltip shelf along with Sales, Budget and Category. Update the Tooltip to suit. Edit the title to reference the Category field, and then name this sheet Gauge – Furniture or similar.

Then duplicate the sheet. Change the Category filter to Office Supplies and name the sheet Gauge – Office. Repeat to create a version for Technology. So you should end up with 3 sheets, one for each Category. Apply the Region, Segment and Order Date filters to be shared across all 3 workbooks.

Building the bar chart

Create a field called

Sales Rank

RANK(SUM([Sales]))

and change it to be discrete (right click > convert to discrete)

Add Category to Columns and Sales Rank to Rows and Sub-Category to Detail. Set the table calculation associated to Sales Rank to be computing by Sub-Category only.

Double click into Columns and manually type MIN(1) to create a ‘fake axis’. Change the mark type to bar and edit the axis to be fixed from 0 to 1. Widen each row slightly. Then move Sub-Category to Label

Then via the Colour shelf, reduce the opacity to 0% and remove the border

Now add Sales to Columns. and on the Sales marks card, move Sub-Category back to Detail, and rest the opacity to 100%

Colour the bars as required. Add Budget to the Detail shelf, then add a reference line to the Sales axis, which shows the Budget per cell as a line with a fill below of light grey.

From one the gauge sheets, set the Region, Segment and Order Date filters to also apply to this sheet.

Add Achievement % to Tooltip and adjust the Tooltip on the Sales marks card only. Remove all the text from the Tooltip on the MIN(1) marks card.

Then format the sheet by

  • editing Sales axis and removing the axis title
  • editing the MIN(1)axis and removing the title and tickmarks
  • hide the sales Rank header (right click pill > uncheck show header
  • hide the Category header
  • remove row & column dividers

Then add the Gauge sheets into a horizontal container on a dashboard, setting the container to ‘distribute contents evenly’. Add the bar chart underneath, but I ‘floated’ it into position as each gauge chart object takes up more vertical space than necessary (the size of the object doesn’t adapt to the fact you’re only showing half a circle).

My published viz is here.

Happy vizzin’!

Donna

Can you use layout containers

Donna ColesLeave a comment

Lorna wanted us to practice layout containers this and also sprinkled in a bit of new functionality – rounded corners! As a result you’ll need to use Tableau Desktop v2026.1 to complete this challenge.

Lorna provided a starter workbook with all the required sheets – we had to build the dashboard.

Describing how to do this is tricky, so I’m going to show a picture of my item hierarchy, and then describe some key points.

Lorna challenged us to use no more than 11 containers. I have 12 as I’ve included my standard ‘footer’ in a horizontal container, which isn’t part of the solution. When working with containers, it’s often useful to add blank objects as placeholders to help ensure the layout. and while you add and position the actual objects. The blank objects then get deleted. Padding is very helpful to reposition objects and add whitespace, but getting the values right can take a bit of trial and error and lots of tweaking.

I have renamed all the containers and numbered them, so you can see the number there are. I’ve named them with an ‘h’ or ‘v’ prefix depending on whether the container is vertical or horizontal.

When dealing with tricky layouts, I always start with a floating container that is positioned at point 0,0 and has the height and width of the dashboard. In this case, this is the container labelled 1.vBase. When I add other containers/objects into this base container, they are set to be tiled. I remove the automatic ’tiled’ container that exists initially, and if any reappear as I’m adding objects, I delete them too.

To generate the bordered shadow effect around the Superstore Overview title, the 2.hHeader container has the following properties:

  • no border
  • background set to light grey
  • corners set to a radius of 5
  • outer padding all 0
  • inner padding all 0

The Superstore Overview title Text object that sits inside this container, then has the following properties

  • no border
  • background set to white
  • corner radius all 0
  • outer padding: left 5, top, bottom, right all 2
  • inner padding all 15

A similar principal is applied to the 11.hRightPane container and the Scatter plot object contained within. The 11.hRightPane container does additionally have a left outer padding of 5 and a bottom padding of 10.

The 4.vLeftPane container just has a right outer padding of 5. This along with the 5 left outer padding of the 11.hRightPane container above gives the 10 pixel spacing between the 2 columns of data in the main body of the chart. There will ultimately be 3 ‘KPI’ rows within this container, so the 4.vLeftPane should be set to distribute contents evenly (only apply once you’ve built all the rows as described below).

Each KPI ‘row’ is constructed in the following manner; I’ll describe the top row, but just repeat for the other rows:

5.hTopKPI-Outer has the following properties

  • no border
  • background of light grey
  • corner radius of 5 all round
  • outer padding all 0
  • inner padding all 0

The 6.hKPI-Inner then has the properties

  • no border
  • background is white
  • corner radius all 0
  • outer padding: left 5, top, bottom, right all 2
  • inner padding all 0

Having the 2 horizontal containers set up this way creates the bordered, shadowed effect.

The Total Sales CY vs PY sheet is then set to a fixed width of 140 and inner padding all round of 10.

The Sales % Change sheet is then set with a fixed width of 130, corner radius of 20 all round, and outer padding of left 15, top 80, right 15 and bottom 20. Note, I also reduced the label text on the sheet itself from 20 to 15 pt.

Finally the Sales over Time sheet is set to have inner padding of 15 all round.

Hopefully all this helps, though as mentioned, I can imagine they’ll be a bit of fiddling to get things right. My published viz is 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

DZV & Filter Actions

Donna ColesLeave a comment

Sean based this week’s challenge on a scenario he’d faced with a client and requires the use of dynamic zone visibility (DMZ) and filter actions.

I’m going to attempt to be relatively brief for the vizzes/views that need building, as the main focus is on the dashboard itself.

Creating the Main KPIs

Connect to the data source and add a data source filter on Country/Region = United States so any data related to Canada gets excluded automatically from everything we build.

Create a new field

Profit Ratio

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

and format to % to 2 dp

On to a new sheet add Sales, Profit, Profit Ratio and Quantity onto the canvas. I just double-clicked each field and once all added used Show Me to display a Text table.

Move Measure Names to Columns and re-order the measures. Add Measure Names to Text too and modify the text so the Measure Names label is beneath Measure Values, aligned centrally and formatted as required. Set the sheet to Entire View. Format the Sales and Profit pills on this sheet to be formatted to $k to 1 dp.

The KPIs need to filter based on the state that will be selected in the map. We’re going to use a parameter to capture the state

pSelectedState

string parameter defaulted to <empty string?

And then we create

Is Selected State?

[pSelectedState]=” OR
[State/Province] = [pSelectedState]

Add this to the Filter shelf and select True.

Format the sheet to remove row/column dividers and hide the Measure Names label header (uncheck show header on the pill).

Name the sheet KPIs-Main or similar.

Creating the Customer KPIs

Although not actually working on Sean’s solution, I believe the intention of showing KPIs on the ‘customer panel’ is to give a summary based on the selected customer. So to do this simply duplicate KPIs-Main and then add Customer Name to the Filter shelf and select all customers. Name the sheet KPIs-Customer or similar.

Creating the Map

ON a new sheet, double click State/Province to automatically create a map and then add Sales to Colour. (if the map doesn’t display the US, then you may need to edit locations – Map > Edit Locations). Adjust the background of the map and remove all city/state labels (Map > Background Layers). Name the sheet Map or similar.

Creating the Customer Orders list

On a new sheet, add Product Name to Rows and then Sales and Quantity as a table. As Is Selected State to Filter and set to True and also add Customer Name to Filter and select all values. Ultimately on click of a state in the map, this list needs to be filtered to just the orders associated to that state , and then additionally be able to be filtered by customer name

Creating the Reset Button

On a new sheet, double click into the space below the marks card and type ‘Reset’ to create a ‘dynamic’ pill. Then change the mark type to square and add this pill onto the Label shelf. Set the size to the largest possible and set the sheet to entire view. Format the label to be centred and larger. Then create a field

State-Reset

”

and add this onto the Detail shelf. This field basically contains an empty string, and we’ll use it later to ‘reset’ the pSelecedState parameter. Call the sheet Reset Button or similar.

Building the dashboard & interactivity

Using layout containers, build out the dashboard as required. I used a horizontal container in the middle of the dashboard which displayed the Map on the left and another Vertical Container on the right. The vertical container is the ‘customer panel’ and it then contained the Customer KPIs, and the Customer Orders sheets along with the Customer Name quick filter (set to a single value dropdown) and reset button in their own horizontal container. My hierarchy is shown below.

Add a dashboard parameter action to select a state

Set State

On select of the Map sheet, set the pSelectedState parameter with the value from the State/Province field. But on clearing the selected keep the current value.

Clicking on a State will now change the KPIs and the list of customer orders

But we also want the list of customers in the filter list to be restricted to those who have orders in the state, so adjust the quick filter settings so that it is set to only relevant values Also customise the control to so the ‘all’ option is not displayed.

Test this behaviour by selecting different States and then checking the values in the Customer Name filter control.

When the Reset button is clicked, we want the State to be deselected and the KPI values all reset, so create another parameter action

Reset State

On select of the Reset Button sheet, set the pSelectedState parameter with the value from the State-Reset field

If you test this out without selecting a Customer Name filter, then the behaviour will work as expected: click a state, KPIs/Orders List and set of customers in the filter change; click reset and KPIs/Orders List and set of customers in the filter all reset .

However if after clicking a state, a customer is then selected in the filter (which filters the Orders List and the Customer KPIs)

If I then click the rest button, the customer panel now shows all the info for the selected customer

and if I now select another state, the customer filter is retained, and if that customer has no orders for the state, I don’t get what I expect

We need to be able to essentially ‘reset’ the customer filter as well when clicking the reset button.

This was the trickiest part of this challenge, and I did try a variety of other ways of displaying the customer filter to see if I could crack it, but I did observe through playing with Sean’s solution and hovering the mouse, that the Customer Name filter was a standard quick filter. Eventually, with the clue being in the challenge title, I got there.

Add a dashboard filter action

Reset Customer Filter

On select of the reset button, target the Customer Orders, KPIs Customer but filter on selected fields where Customer Name = Customer Name

Finally we want the customer panel to only display once the map is clicked. For this, create a new field

State parameter is not empty?

[pSelectedState] <> ”

which will be true when pSelectedState contains the name of a state which happens ‘on click’ of the map. Then back on the dashboard, select the ‘customer panel’ container and on the Layout section, check the control visibility using value option and select the above field

And fingers crossed, you should now have a functioning viz. 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 account for nulls in your averages?

Donna ColesLeave a comment

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

Setting up the core data requirements

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

Category Hierarchy

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

The display shows the number of orders, so we need

#Orders

COUNTD([Order ID])

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

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

pMinDate

date parameter defaulted to 11 Jul 2025

and

pMaxDate

date parameter defaulted to 23 Jul 2025

with these we create

#Orders in Date Range

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

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

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

Index

INDEX()

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

Adding the average

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

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

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

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

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

Formatting the rest of the table

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

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

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

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

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

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

Number prefix *

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

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

The create

Tooltip – 0 orders

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

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

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

Creating the date filter & Apply button

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

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

Create new fields

Min Date

MIN([Order Date])

and

Max Date

MAX([Order Date])

and add both to the Detail shelf.

Create a new field

Colour

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

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

Finally create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf.

Building the dashboard

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

Create the following dashboard actions:

Set Min Date

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

Set Max Date

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

Deselect Button

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

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

My published viz is here.

Happy vizzin’!

Donna

KPI Trend Monitor With Period Comparison

Donna ColesLeave a comment

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

Defining the calculations

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

pToday

date parameter defaulted to 04 Feb 2025.

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

Today Last Month

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

Today Last Year

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

I also want to group these ‘timeframes’ and created

Recent | Prior Mth | Prior Yr

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

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

Create a new field

Profit Ratio

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

and format to % with 1 dp

Additionaly create fields

PR -Recent

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

and

PR – Not Recent

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

and format these to % with 1dp too.

Add these 3 fields into the table

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

X-Axis

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

Add this onto Rows

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

Building the Line Chart

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

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

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

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

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

Building the KPI Card

Create new fields

PR-Today

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

format to % with 1 dp

PR-Yesterday

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

format to % with 1 dp

PR – Difference

[PR – Today] – [PR – Yesterday]

format to % with 1 dp

PR Direction Up

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

PR Direction Down

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

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

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

Happy vizzin’!

Donna

Can you create a dynamic moving average chart?

Donna ColesLeave a comment

Lorna set a table calculation focused challenge this week.

Creating the parameters

The challenge requires 3 parameters

pTimePortion

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

pTimeFrame

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

pMoveAvg

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

Creating the calculations

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

Display Date

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

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

Moving Avg

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

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

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

Latest Date

WINDOW_MAX(MAX([Display Date]))

then fed this into

Date to Display

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

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

Building the viz

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

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

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

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

Finally, tidy up by

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

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

Happy vizzin’!

Donna

Can you create KPI cards with viz extensions?

Donna ColesLeave a comment

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

Creating the Sales KPI Card

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

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

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

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

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

which results in

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

Creating the Orders KPI card

Create a new field

# Orders

COUNTD([Order ID])

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

Creating the Profit Ratio KPI Card

Create a new field

Profit Ratio

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

and format to % with 1 dp

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

Creating the Filter Control

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

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

Building the dashboard

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

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

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

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

Add a dashboard filter action

Filter

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

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

My published viz is here

Happy vizzin’!

Donna