Can you compare the latest month to the prior X months average?

Donna ColesLeave a comment

Lorna set this challenge this week to test some LoD fundamentals. My solution has a mix of LoDs and table calculations – there weren’t any ‘no table calcs’ allowed instructions, so I assumed they weren’t off limits and in my opinion, were the quickest method to achieve the line graph.

Note – For this challenge, I downloaded and installed the latest version of Tableau Desktop v2022.2.0 since Lorna was using the version of Superstore that came with it. The Region field in that dataset was set to a geographic role type. I built everything I describe below using the field fine, but when I extracted the data source at the end and ‘hid all unused fields’ before publishing, the Region field reported an error (pill went red, viz wouldn’t display). To resolve, I ended up removing the geographic role from the field and setting it just to be a string datatype. At this point I’m not sure if this an ‘unexpected feature’ in the new release…

Ok, let’s get on with the build.

Building the basic viz

I started by building out the basic bar & line chart. Add Region to Rows, Order Date as continuous month (green pill) to Columns and Sales to Rows. Change the mark type to bar, change the Size to manual and adjust.

Drag another copy of the Sales pill to Rows, so its next the other one. Click on the context menu of that 2nd pill, and select Quick Table Calculation -> Moving Average

Change the mark type of the 2nd Sales pill to line.

Now click on the context menu of the 2nd Sales pill again and Edit Table Calculation. Select the arrow next to the ‘Average, prev 2, next 0’ statement, and in the resulting dialog box, change the Previous values to 3 and uncheck the Current Value box

At this point you can verify whether the values match Lorna’s solution when set to 3 previous months.

But, we need to be able to alter the number of months the moving average is computing over. For that we need a parameter

pPriorMonths

integer parameter, default to 3, that ranges from 2 to 12 in steps of 1.

Then click on the 2nd Sales pill and hold down Shift, and drag the pill into the field pane on the left hand side. This will create a new field for you, based on the moving average table calculation. Edit the field. Rename it and amend it so it references the pPriorMonths parameter as below

Moving Avg Sales

WINDOW_AVG(SUM([Sales]), -1*[pPriorMonths], -1)

Adjust the tooltip for both the line and the bar (they do differ). Ignore the additional statement on the final bar for now.

Colour the line black and adjust size. Then make the chart dual axis and synchronise axis. Hide the right hand axis. Remove Measure Names from the Colour shelf of both marks cards.

Colouring the last bar

In order to colour the last bar in each row, we need 3 pieces of information – the value of Sales for the last month, the moving average value for the last month, and an indicator of whether one is bigger than the other. This is where the LoDs come in.

First up, lets work out the latest month in the dataset.

Latest Month

DATE(DATETRUNC(‘month’,{FIXED : MAX([Order Date])}))

finds the latest date in the whole dataset and truncates to the 1st of the month. Note, this works as there’s sales in the last month for all Regions, if there hadn’t been, the calculation would have needed to be amended to be FIXED by Region.

From this, we can get the Sales for that month for each Region

Latest Sales Per Region

{FIXED [Region] :SUM( IF DATETRUNC(‘month’, [Order Date]) = [Latest Month] THEN [Sales] END)}

To work out the value of the moving average sales in that last month, we want to sum the Sales for the relevant number of months prior to the last month, and divide by the number of months, so we have an average.

First let’s work out the month we’re going to be averaging from

Prior n Month

DATE(DATEADD(‘month’, (-1 * [pPriorMonths]),[Latest Month]))

This subtracts the relevant number of months from our Latest Month, so if the Latest Month is 01 Dec 2022 and we want to go back 3 months, we get 01 Sept 2022.

Avg Sales Last n Months

{FIXED [Region]:SUM( IF DATETRUNC(‘month’, [Order Date]) >= [Prior n Month] AND
[Order Date] < [Latest Month] THEN [Sales] END)} / [pPriorMonths]

So assuming we’re looking at prior 3 months, for each Region, if the Order Date is greater than or equal to 01 Sept 2022 and the Order Date is less than 1st Dec 2022, get me the Sales value, then Sum it all up and divide by 3.

And now we determine whether the sales is above or below the average

Latest Sales Above Avg

SUM([Latest Sales Per Region]) > SUM([Avg Sales Last n Months])

If you want to sense check the figures, and play with the previous months, then pop the data into a table as below

So now we’re happy with the core calculations, we just need a couple more to finalise the visualisation.

If we just dropped the Latest Sales Above Avg pill onto the Colour shelf of the bar chart, all the bars for every month would be coloured, since the calculation is FIXED at the Region level, and so the value is the same for all rows associated to the the Region. We don’t want that, so we need

Colour Bar

IF DATETRUNC(‘month’, MIN([Order Date])) = MIN([Latest Month]) THEN
[Latest Sales Above Avg]
END

If it’s latest month, then determine if we’re above or below. Note the MIN() is required as the Latest Sales Above Avg is an aggregated field so the other values need to be aggregated. MAX() or ATTR() would have worked just as well.

Add this field to the Colour shelf of the bar marks card and adjust accordingly.

Sorting the Tooltip for the last bar

The final bar has an additional piece of text on the tooltip indicating whether it was above or below the average. This is managed within it’s own calculated field.

Tooltip: above|below

IF DATETRUNC(‘month’ ,MIN([Order Date])) = MIN([Latest Month]) THEN
IF [Latest Sales Above Avg] THEN ‘The latest month was above the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’
ELSE ‘The latest month was below the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’
END
END

If it’s the latest month, then if the sales is above average, then output the string “The latest month was above the prior x month sales average” otherwise output the string “The latest month was below the prior x month sales average”.

Add this field onto the Tooltip shelf of the bar marks card, and amend the tooltip text to reference the field.

Finalise the chart by removing column banding, hiding field labels for rows, and hiding the ‘4 nulls’ indicator displayed bottom right.

Creating an Info icon

On a new sheet, double click into the space within the marks card that is beneath the Detail, Tooltip, Shape shelves, and type in any random string (”, or ‘info’ or ‘dummy’). Change the mark type to shape and select an appropriate shape. I happened to have some ? custom shapes, so used that rather than create a new one. For information on how to create custom shapes, see here. Amend the tooltip to the relevant text. When adding to the dashboard, this sheet was just ‘floated’ into the position I was afte. I removed the title and fit to entire view.

My published viz is here.

Happy vizzin’!

Donna

Can you build a dumbbell chart?

Donna ColesLeave a comment

Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.

So here’s what I did.

Defining the calculations required

I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.

I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie

Ownership 2015

IF [Year]=2015 THEN [Ownership] END

Ownership 2019

IF [Year]=2019 THEN [Ownership] END

Both these fields I formatted to % with 0dp.

Given these, I can then work out the difference

Difference

SUM([Ownership 2019])-SUM([Ownership 2015])

also formatted to % with 0dp

and finally, I can work out whether the change exceeds 20% or not

Difference>0.2

[Difference]>0.2

These are all the fields required 🙂

Building the Dot Plot

Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.

Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.

This will have the effect of adding Measure Names and Measure Values to the view.

Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.

Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.

Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of ▲0%;▼0% to the Difference field that was on the Tooltip shelf.

So my tooltip was

which generated a tooltip that looked like

Connecting the dots

Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.

On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.

Change the mark type to Line and add Measure Names to the Path shelf.

Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.

Delete all the text from the Tooltip of the ‘line’ marks card.

Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.

To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.

Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.

Adding Age to be displayed at the top

The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.

Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂

Additional Note

If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg

Ownership 2015

{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}

and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.

My published viz is here.

Happy vizzin’!

Donna

Let’s Build a KPI Dashboard

Donna ColesLeave a comment

Sean set this fun and very relevant challenge this week displaying key measures (KPIs) along with further details for a selected measure displayed in a horizontal layout rather than the more traditional long-form, using ranking. The ‘gotcha’ part of this challenge was ensuring the tooltips displayed the measures in the right format, ie $ for Sales and Profit, % for Profit Ratio and a standard number to 0 dp for # of Orders.

We’ll start by

Building the KPI sheet

The data source I connected to already had Profit Ratio included, but if yours doesn’t, you’ll need to create as

Profit Ratio

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

You’ll also need to create

# of Orders

COUNTD([Order ID])

On a new sheet, double-click Sales and then double-click Profit to add them both to a sheet, and then use Show Me to display the fields as a text table.

Move Measure Names from Rows to Columns, then add Profit Ratio and # of Orders into the Measure Values section under the marks card. Re-order the measures into the required order.

Modify the format of each pill in the Measure Values section to the relevant format (millions, thousands, %)

Add Measure Names to the Text shelf, and adjust the text to be aligned middle centre and resize the fonts.

Hide the header (uncheck Show Header against the Measure Names in the Columns, and remove row dividers. Set the Tooltip not to show.

Building the Ranked Chart

The value to display in this chart is dependent on the KPI measure clicked on. We’re going to use a parameter to help drive this behaviour, so start by creating

Measure to Display

string parameter defaulted to ‘Sales’ containing 4 options: Sales, Profit, Profit Ratio, # of Orders

Based on the parameter, we need to determine which value to display

Selected Value

CASE [Measure to Display]
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Profit Ratio’ THEN [Profit Ratio]
WHEN ‘# of Orders’ THEN [# of Orders]
END

On a new sheet, add Sub-Category to Rows and Selected Value to Text and sort by Sub-Category descending. Show the Measure to Display parameter on the sheet and play around with the different options to see how the viz changes. When you’re happy it’s working as expected, reset back to ‘Sales’.

We’ll need to display the rank value, so create

Rank Value

RANK_UNIQUE([Selected Value])

and format this to be a number with 0dp but pre-fixed by #

Add this into the table.

Now, to display this information, we need to distribute each Sub-Category across 5 rows and 4 columns which is based on the Rank Value. So let’s work out which row and column each entry should be in

Rows

IF [Rank Value]%5 = 0 THEN 5 ELSE [Rank Value]%5 END

If the rank is divisible by 5, then place in the 5th row, otherwise place in the row associated to the remainder when divided by 5.

Cols

IF [Rank Value] <=5 THEN 1
ELSEIF [Rank Value] <=10 THEN 2
ELSEIF [Rank Value] <=15 THEN 3
ELSE 4
END

Add these fields to the table, so you can see how they’re working.

The Rank Value, Rows and Cols fields are all table calculations, and should be set to explicitly compute using Sub-Category.

On a new sheet, add Sub-Category to Detail, Cols to Columns as a blue discrete pill and Rows to Rows as a blue discrete pill. Add Selected Value to Columns. Drag Sub-Category from Detail on to Label and adjust to align the labels left. Widen each row slightly, so the text is visible. Adjust the Colour of the bars to be a pale grey.

Change the Measure to Display parameter to ‘Profit’, and notice what happens to the bars & labels associated to those with a negative profit. They’re positioned where you’d expect, on the negative axis, but this doesn’t match Sean’s solution.

Sean has chosen to display the values as absolute values (although the tooltips display the negative values). This means we need

Abs Value to Display

ABS([Selected Value])

Drag this field and drop directly on top of the Selected Value pill on the Columns shelf – this will replace the measure being displayed.

Now that’s sorted, we can get the ranking added.

Double click in the space next to Abs Value to Display on Columns and type in MIN(0) to create an additional axis and create a 2nd marks card.

On the MIN(0) marks card, move Sub-Category to the Detail shelf and then add Rank Value to the Label shelf. Make sure the table calc is set to compute by Sub-Category as before.

Change the mark type of the MIN(0) card to Gantt Bar, set the size to the smallest possible, and colour opacity to 0. The change of the mark type, will have changed the position of the rank label to be aligned to the left of the mark.

Make the chart dual axis, synchronise the axis and change the mark type of the Abs Value to Display back to Bar. Remove Measure Names from both of the marks cards.

Hide both axis and the headers and remove grid lines and divider lines. Make the zero line on the columns a solid line, slightly thicker.

Formatting the Tooltips

The tooltips display the selected measure name and associated value, formatted appropriately. As we’re using a ‘generic’ field to display the value, we can’t format this field as we’d usually do. Instead, I chose to resolve this using dedicated fields to store each value based on the measure selected.

Tooltip: Sales

IF [Measure to Display]=’Sales’ THEN [Sales] END

format this to $ with 0dp.

Tooltip: Profit

IF [Measure to Display]=’Profit’ THEN [Profit] END

format this to $ with 0dp.

Tooltip: Profit Ratio

IF [Measure to Display]=’Profit Ratio’ THEN [Profit Ratio] END

format this to 5 with 2 dp

Tooltip: #Orders

IF [Measure to Display]=’# of Orders’ THEN [# of Orders] END

format this to a number with 0 dp.

Add all these fields to the Tooltip shelf on the Abs Value to Display marks card, and then amend the tooltip so all these fields are listed side by side with no spacing. As only one of the fields will only ever contain a value, the correctly formatted figure will display. The Tooltip will also need to reference the Measure to Display parameter to act as the ‘label’ for the value displayed.

Finally adjust the title of the sheet to also reference the Measure to Display parameter,

Adding the interactivity

Add the sheets to a dashboard, then add a parameter action

Select Measure

On select of the KPI sheet, update the Measure to Display parameter, passing through the Measure Names value. When the measure is unselected, revert the display back to ‘Sales’.

Bonus – Colour the bars

As an added extra to call out the fact the negative values were being displayed on the positive axis, my fellow #WOW participant, Rosario Gauna, chose to colour the bars.

Colour Bar

[Selected Value] < 0

Add this to the Colour shelf of the Abs Value to Display marks card and adjust colours according.

My published viz is here. Enjoy!

Happy vizzin’!

Donna

Dynamic Dates in a Heat Map

Donna ColesLeave a comment

Erica set the challenge this week using a custom made dataset. The focus was to handle viewing different aggregations of the dates based on the date range selected, ie for short date ranges seeing the data at a day level, for medium ranges, view at the week level and then at a monthly level for larger ranges. This type of challenge has been set before, but the visual is typically a line chart rather than a heat map. The principles are similar though.

Determining the Date to Display

Each row in the data set has a Date associated to it. For this challenge we need to ‘truncate’ the date to the appropriate level depending on the date range and the date level selected by the user. By that I mean that if the level is deemed to be weekly, we need to ‘reset’ each date to be the 1st day of the week.

To start with, we need to set up some parameters which drives the initial user input.

pStart

Date parameter defaulted to 11/07/2022 (11th July 2022), with a display format of <weekday>, <date>

pEnd

As above, but defaulted to 24/07/2022 (24th July 2022).

pDateLevel

integer with values 0-3 as listed below, with a text Display As. Defaulted to ‘Default’. Note, I am using integers as we will be referencing them in IF statements later, and integers are more performant than comparing strings.

With these parameters, we can then build

Date To Display

DATE(
IF [pDateLevel] = 0 THEN
IF DATEDIFF(‘day’, [pStart], [pEnd]) < 28 THEN DATETRUNC(‘day’,[Date])
ELSEIF DATEDIFF(‘day’, [pStart], [pEnd]) < 90 THEN DATETRUNC(‘week’, [Date])
ELSE DATETRUNC(‘month’, [Date])
END
ELSEIF [pDateLevel] = 1 THEN DATETRUNC(‘day’,[Date])
ELSEIF [pDateLevel] = 2 THEN DATETRUNC(‘week’,[Date])
ELSE DATETRUNC(‘month’, [Date])
END
)

If the pDateLevel is set to 0 (ie Default), then compare the difference between the dates entered and truncate to the ‘day’ level if the difference is less than 28 days, the ‘week’ level if the difference is less than 90 days, else truncate to the month level (which will return 1st of the month). Otherwise, if the pDateLevel is 1 (ie Day), truncate to the day level, if it’s 2 (ie Week), truncate to the week level, else use the month level.

To see how this field is working, add Date and Date To Display to Rows, both as discrete exact dates (blue pills), display the parameter fields, and adjust the values. Below you can see that using the Default level, between 1st May and 24th July, the 1st day of the associated week against each date is displayed.

Building the Bar Chart

This is the simpler of the two charts to build, so we’ll start with this.

Add Employee Name to Rows, and Minutes to Columns. and sort by Minutes descending (just click the sort descending button in the toolbar). Add Project to Filter, select ‘A’ and then show the filter. Adjust the colour of the bars. I used #311b92

We need to restrict the data displayed based on the date range defined.

Dates to Include

[Date]>=[pStart] AND [Date]<=[pEnd]

Add this to the Filter shelf and set to True.

Now, we need to label the bars, and need new fields for this.

Duration (hrs)

FLOOR(SUM([Minutes])/60)

Using the FLOOR function means the value is always rounded down to the relevant whole number (eg 60.5 and above will still result in 60).

Format this field to be 0 decimal places with a ‘h’ suffix

Duration (mins)

SUM([Minutes])%60

Modulo (%) 60 means return the remainder when divided by 60. Format this to be 0 dp.

Add both these fields to the Label shelf, and adjust so the labels are displaying on the same line and are aligned to the left. You may need to increase the width of each row to see them.

Add Project to the Tooltip shelf and update, then remove all gridlines and the axis. Leave the Employee Name column visible for now. We’ll come back to this later.

Building the Heat Map

On a new sheet, add Employee Name to Rows and Date to Display as a discrete exact date (blue pill) to Columns. Add Minutes to Colour and a heat map should automatically display.

Go back to the bar sheet, and set both the Project and the Dates To Include filters to apply to the heat map worksheet as well (right click each pill on the filter shelf -> Apply to Worksheets -> Selected Worksheets and select the other one you’re building).

Then click the sort descending button in the toolbar, to order the data as per the bar.

Add both Duration (hrs) and Duration (mins) to the Label shelf and change the mark type to Square. Adjust the label so it is on the same line and left aligned (I added a couple of spaces to the front of the text so it isn’t so squashed).

Edit the colours of the heat map – I used a colour palette I had installed called Material Design Deep Purple Seq which seemed to match, but may not be installed by default.

Format the main heat map section to have a light grey background by right clicking on the table -> format and setting the shading of the pane only to pale grey

Then adjust the row dividers of the pane to be white, and the header to be pale grey (set the level to the max).

Adjust the column dividers similarly, setting the pane to be white, the header to none, and level to max

Next we want to deal with the column labels. By default they’re showing the date, but we want this to show something different depending on the date level being displayed.

Column Label

IF (([pDateLevel] = 1) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<28))) THEN UPPER(LEFT([Weekday],3))
ELSEIF (([pDateLevel] = 2) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<90))) THEN ‘w/c ‘ + IIF(LEN(STR(DATEPART(‘day’, [Date To Display])))=1,’0’+STR(DATEPART(‘day’, [Date To Display])), STR(DATEPART(‘day’, [Date To Display]))) + ‘-‘ + UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3))
ELSE UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3))
END

If the date level is ‘day’ or the date level is the default and the start & end are less than 28 days apart, then show the day of the week (1st 3 letters only in upper case).

Else if the date level is ‘week’ or the date level is the default and the start & end are less than 90 days apart, then show the text ‘w/c’ along with the day number (which should be 01 to 09 if the day is < 10) a dash (-) and then the 1st 3 letters of the month in upper case.

Else, we’re in monthly mode, so show the 1st 3 letters of the month in upper case.

Add this to the Columns shelf, then hide the Date To Display field (uncheck show header), hide field label for columns and hide field label for rows. Format the Employee Name field so its a different font (I changed to Tableau Book).

Again play around with the parameters and see the changes to the column label.

Finally, add Project to the Tooltip and update. You’ll need to adjust the formatting of the Date To Display field to get it into <day of week>, <date> format.

Adding to the Dashboard

When you add the two charts to the dashboard, you’ll need to set them side by side within a horizontal layout container. Remove the titles. They both need to be set to ‘fit entire view’, and the width of the heat map chart should be fixed (I set it to 870px), so it retains the space it stays in, even when you only have 1 month displayed.

Once you’re happy the order of the heat map employees matches the order of your bar chart, uncheck show header against the Employee Name field on the bar chart.

To get the bars to align with the heat map, I showed the title of the bar, removed the text and just entered a space. This dropped the bars so they just about aligned. You may need to tweak by increasing the size of the bars slightly.

Finally, you need to move your parameters around. I placed them in a horizontal container, whose background colour was set to pale grey. I then set the objects within to be equally spaced by setting the container to Distribute Contents Evenly.

I then altered the padding of each of the parameter objects to have outer padding = 0 and inner padding = 5 and added a pale grey border surrounding each.

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

Happy vizzin’!

Donna

What is the time-weighted return?

Donna ColesLeave a comment

Indeed! What is the time-weighted return? It’s not a term I’d ever heard of until Luke set the challenge this week. Fortunately he provided a link to a web page to explain all about it – phew!

I actually approached the challenge initially by downloading the data source, copying the contents of sheet2 into a separate excel workbook, and then stepping through the example in the web page, creating new columns in the excel sheet for each part of the calculation. I didn’t apply any rounding and ended up with slightly different figures from Luke. However after a chat with my fellow WOW participant Rosario Gauna, we were both aligned, so I was happy. I’d understood the actual calculation itself, so I then cracked open Desktop to begin building. This bit didn’t really take that long – most of the time on this challenge was actually spent in Excel, but shhhhh! don’t tell anyone 🙂

Building the calculations

I’m going to build lots of calculated fields for this, primarily because I like to do things step by step and validate I’m getting what I expect at each stage. You could probably actually do this in just 2 or 3 calcs. As I built each calc, I added it into a tabular view of the data. I’m not going to screen shot what the table looks like after every calc though.

To start though, I chose to rename some of the existing fields as follows :

  • Flows -> A: Flows
  • Start Value -> B: Start Value
  • End Value -> C: End Value

I did this because I have several new fields to make, and coming up with a meaningful name for each was going to be tricky – you’ll understand as you read on 🙂

First up, create a sheet and add Reporting Date as an exact date discrete blue pill to Rows then add the above 3 fields.

Now onto the calculations, and this all just comes from the website. Each calc builds on from the previous. I chose to add inline comments as a reminder. After I built each calc I added to the table.

D: A+B

//sum flow + start point
SUM([A: Flows]) + SUM([B: Start Value])

E: C-D

//difference between end value and (flow+start)
SUM([C: End Value]) – [D: A+B]

F: E/D

//Difference as a proportion of (start + flow)
[E: C-D]/ [D: A+B]

I formatted this one to 3 decimal places so the figure showed on screen, but ‘under the bonnet’ Tableau will use the full number in the onward calcs.

At this point now, F: E/D stores what the article describes as the ‘HP’ value for each date. Now we need to start building the calcs we need to compute the TWR value.

G: 1+F

1+[F: E/D]

formatted to 3 dp

H: Multiply G values

//Running Product of column G
[G: 1+F]*PREVIOUS_VALUE(1)

This is the core calculation for this challenge. This takes the value of the G: 1+F field in each row, and multiples it by the value of the G: 1+F field in the previous row. As each row ends being the product of itself and the former row, this ultimately builds up a ‘running product’ value.

Add these fields onto the table and explicitly set the table calculation of the H: Multiply G values field to compute using Reporting Date.

And finally we can create

I: TWR

//subtract 1 from the running product -1
[H: Multiply G values]-1

format this to be a % with 0dp. Add to the table and again ensure the table calc (and all nested calcs) are set to compute using Reporting Date

The final calculation needed is one to use to display the TWR value for the last date.

J: Latest TWR Value

//get the last value computed
WINDOW_MAX([I:TWR])

format this to be a % with 0dp.

This function looks for the highest number across the whole table, and spreads that value across every row. Since the I: TWR field is cumulative, the highest number will be the last value. Add this onto the table and verify all the tab calcs are set to compute by Reporting Date.

And now you can build the viz

Building the Viz

Add Reporting Date as an exact date continuous green pill to Columns and I:TWR to Rows, setting the compute by of the table calculation as described above. Add J:Latest TWR Value to Detail, and again set the compute by.

Then update the title of the sheet to reference the J:Latest TWR Value field, amend the tooltips and update the label on the y-axis. Add to a dashboard and you’re done 🙂

My published viz is here.

Happy vizzin’!

Donna

Can you build a butterfly chart?

Donna ColesLeave a comment

Lorna Brown set the challenge this week to build this butterfly chart, so called because of the symmetrical display. I’ve built these before so hoped it wouldn’t be too taxing.

The data set isn’t very verbose, fields for gender & year specific values by age bracket

When connecting to this excel file, you need to tick the Use Data Interpreter checkbox which removes all the superfluous rows you can see in the excel file.

Now, I started by building a version that required no further data reshaping, which I’ve published here. This version uses two sets of dual axis, and I use the reversed axis feature to display the male figures on the left. This version required me to float the Population axis label onto the dashboard so it was central. Overall it works, the only annoying snag is that I have two 0M labels displayed on the bottom axis, since this isn’t a single axis. As a result, I came up with an alternative, which matches the solution, and which I’m going to blog about.

Reshaping the data

So once I connected to the data and applied the Use Data Interpreter option, I then selected the Males, 2021 and Females, 2021 columns, right clicked and selected Pivot

This results in a row for the Male figures and a row for the Female figures, with a column Pivot Names indicating the labels for each row. I renamed this to Gender, and the Pivot Values column to Population.

Building the Chart

On a sheet, add Age to Rows and Population to Columns and Gender to Colour. Set Stack Marks Off (Analysis > Stack Marks > Off).

You’ll see that both the values for the Male & Female data is displaying in the positive x-axis. We don’t want this. So let’s create

Population – Split

IF [Gender]= ‘Males, 2021’ THEN [Population]*-1 ELSE [Population] END

Replace the Population pill on Columns with Population Split

So this is the basic butterfly, but now we need to show the Total Population. Firstly, let’s create

Total Population per Age Bracket

{FIXED Age: SUM([Population])}

And then similarly to before, we need to display this in both directions against the Male and the Female side, so create

Total Population Split

IF [Gender]= ‘Males, 2021’ THEN [Total Population per Age Bracket]*-1 ELSE [Total Population per Age Bracket] END

Drag this field onto the Population Split axis until the two green column icon appears, then let go of the mouse. This is creating a combined axis, where multiple measures are on the same axis.

Move Measure Names from Rows to the Detail shelf and then change the symbol to the left, so the pill is also added to the Colour shelf. Adjust the colours of the 4 options accordingly.

Edit the axis and rename to Population. Then Format the axis, and set the scale to display in millions to 0dp

The tooltip needs to display % of total, so we’ll need

% of Total

SUM([Population]) / SUM([Total Population per Age Bracket])

Add this to the Tooltip shelf

We also need the population figures on the tooltip displayed differently, so explicitly add Population Split and Total Population Split to the Tooltip shelf. Use the context menu of the pills on this shelf, to set the display format in the pane to be millions to 2 decimal places. But this time, once set using the Number(Custom) dialog, switch to the Custom option and remove the – (minus sign) from the format string. This will make the values displayed against the Males which are plotted on the -ve axis, to show as a positive number.

Finally, set aliases against the Gender field, so it displays as Males or Females (right click on Gender > Aliases).

Now you have all the information to set the tooltip text.

Now add a title to the sheet to match that displayed, hide field labels for rows, and adjust any other formatting with row/column dividers etc that may be required, and add to a dashboard.

And that should be it. My published version of this build is here.

Happy vizzin’!

Donna

Can you make a bullet chart?

Donna ColesLeave a comment

Kyle returned this week to set this challenge to create a bullet chart which compared Win % for baseball teams in a year (bar) to the previous year (line), along with call out indicators (red circles) if the difference was greater than a user defined threshold. To add an extra ‘dimension’ to the challenge, a viz in tooltip showing the historical profile for the selected team was also required.

Building the basic bar chart

We’re going to use a parameter to define which year we want to analyse, as we can’t just filter by the Year as we need information about multiple years

pYear

integer field defaulted to 2018, formatted so the thousands separator does not display, and populated by Add values from the existing Year field

With this, we can create the following fields

Wins – CY

IF Year = [pYear] THEN [Wins] END

Wins – PY

IF Year = [pYear]-1 THEN [Wins] END

Games – CY

IF Year = [pYear] THEN [Games] END

Games – PY

IF Year = [pYear]-1 THEN [Games] END

and subsequently

Wins per Game – CY

SUM([Wins – CY]) / SUM([Games – CY])

and

Wins per Game – PY

SUM([Wins – PY]) / SUM([Games – PY])

Both these 2 fields are formatted to a custom format of ,##.000;-#,##.000 (this basically strips off the leading 0, so rather than 0.595 it’ll display as .595.

Now we have all these, we can build a dual axis chart.

Add League and Team to Rows and Wins per Game – CY to Columns. Sort by Wins per Game – CY descending. Display the pYear parameter.

Add Wins per Game – PY to Columns, make dual axis and synchronise the axis. Change the mark type of the CY card to a bar and the mark type of the PY card to Gantt. Remove Measure Names from the Colour shelf of both cards, and change the colour of the gantt bar to black.

Show mark labels for the bar mark and align left (expand the width of each row if need be).

Colouring the bars

The colour of the bars is based on whether the CY value is greater or less than the PY value. So we need to find the difference

CY-PY DIfference

[Wins per Game – CY]-[Wins per Game – PY ]

and then work out if the difference is positive or not

CY-PY Difference is +ve

[CY-PY Difference]>0

Add this to the Colour shelf of the bar marks card and adjust accordingly.

Adding the call out indicator

The red circle is based on whether the difference is above a threshold set by the user . A parameter is required for this

pChange

float field set to 0.1 by default; the values should range from 0.05 to 0.2 in 0.05 intervals

Show this parameter on the sheet.

We then can create

CY-PY Diff Greater than Change

IF ABS([CY-PY Difference])>[pChange] THEN ‘●’ ELSE ” END

The ABS function is used, as we want to show the indicator regardless as to whether the difference is a +ve or -ve difference. The ● image I get from copying from https://jrgraphix.net/r/Unicode/25A0-25FF. I use this page a lot, so keep it bookmarked.

Add this field to Rows, and then format the field so it is red and the font size is bigger (I used 12pt)

Now the viz just needs to be tidied up by

  • Hide field labels for rows
  • Rotate label of the League field
  • Format the font of the Team text
  • Reduce the width of the first three columns
  • Remove gridlines and zero lines
  • Adjust the row divider to be a dotted line at the 2nd level
  • Remove column dividers
  • Add an axis ruler to Rows
  • Uncheck Show Header on the axis to hide them
  • Tidy up the tooltip on the gantt mark type.

Building the Viz in Tooltip line chart

For this we need the Win % for every year, so we need

Wins per Game

SUM([Wins]) / SUM ([Games])

Add Team to Rows, Year to Columns (change to be a continuous, green pill) and Wins per Game to Rows

Add pYear to the Detail shelf, then add a Reference line to the Year axis to display the value of pYear as as dotted line

Right click on the reference line > format and adjust the alignment of the value displayed to be top centre.

Show mark labels and set to just show the min & max values for each line.

Colouring the lines

The colour of the line is based on whether the current year’s value is bigger or smaller than the previous year (ie the colour of the line matches the bar).

However, we can’t just use the fields we’ve already built, as because we have Year in the view, the data only exists against the appropriate year, so the difference can’t be computed. You can see this better if you build out the table below…

We need to ‘spread’ these values across every year for each team.

Wins per Game – CY Win Max

WINDOW_MAX(SUM([Wins – CY]) / SUM([Games – CY]))

Format this as you did above.

Add this to the view and amend the table calculation so it is computing by the Year field only. You should see that the value of this field matches the Wins per Game – CY value, but the same value is listed against every year now, rather than just the one selected.

Similarly, create

Wins per Game – PY Win Max

WINDOW_MAX(SUM([Wins – PY]) / SUM([Games – PY]))

format, and add this to the view too and adjust the table calculation as you did above.

So now we have this, we can work out whether the difference between these two fields is +ve or not

CY-PY Difference is +ve Win Max

[Wins per Game – CY Win Max]- [Wins per Game – PY Win Max]>0

Add this to the Colour shelf of the line chart. Verfify the table calculation is set to compute by Year only for both nested calculations, and then adjust the colours to match.

Finally tidy up the viz, to remove all headings, axis, gridlines, row/column dividers etc Set the background colour of the sheet to a light grey, then finally set the fit to be Entire View.

Adding the Viz in Tooltip

Go back the bar chart, and on the tooltip of the bar mark, adjust the initial text, then insert the line chart sheet via the Insert > Sheets > Sheetname option.

I adjusted the maxwidth & maxheight properties of the inserted text to be 350px each

If you hover over the bar chart now, you should get a filtered view of the line chart.

Final step is to put all this on a dashboard. My published viz is here.

Happy vizzin’!

Donna

Let’s make a dynamic relative date filter!

Donna ColesLeave a comment

Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…

The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.

I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.

So let’s crack on.

Building the initial chart

I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.

We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection

pDateLevel

string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.

Then we need a new date field to show the dates at the level specified

Display Date

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

Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.

Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.

Setting up a scaffold data source

So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.

I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets

DateParts

A single column headed Datepart containing the values Year, Quarter, Month, Week, Day

Relative Options

3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)

I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).

Building the Date Part Selector

From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.

Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.

We’re going to need another parameter to capture the date part that will be clicked.

pDatePartSelected

string parameter defaulted to ‘quarter’ (note case)

We also need a new calculated field to store the value of the date parts in lower case

Datepart Lower

LOWER([Datepart])

and then we can use these fields to work out which date part has been selected on the viz

Selected Date Part

[pDatePartSelected] = [Datepart Lower]

Add this to the Colour shelf, and adjust colours accordingly (False is white).

Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.

Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.

In the DateParts data source, create new calculated fields

True

TRUE

False

FALSE

and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.

Building the Relative Date Radio Button Selector

In the Relative Options data source, create the following fields

Row

IF ([Option ID] %3) = 0 THEN 3
ELSE ([Option ID] %3)
END

Column

INT([Option ID]<=3)

These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.

We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed

pSelectPeriods

integer parameter

We can now create

Option Label

IF [pDatePartSelected] = ‘day’ THEN
IF [Option ID]=1 THEN ‘Yesterday’
ELSEIF [Option ID] =2 THEN ‘Today’
ELSEIF [Option ID] = 3 THEN ‘Tomorrow’
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
ELSE
IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected]
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
END

This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…

Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed

We’re going to need to identify which of the options has been selected. This will be captured in a parameter

pOptionSelected

integer parameter defaulted to 2

and then we need to know if the row of data matches that selected, so create a calculated field

Selected Relative Date Option

[Option ID] = [pOptionSelected]

Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.

As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.

Restricting the dates displayed

Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter

pAnchorDate

date parameter, defaulted to 31 Dec 2022

From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.

In the Superstore data source, create

Compare Date

DATE(DATETRUNC([pDatePartSelected],[pAnchorDate]))

Then from this, I created

Dates To Show

CASE [pOptionSelected]
WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])

WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))

WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))

WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])

WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))

WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate])
ELSE FALSE
END

This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.

If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes

Building the data range display

On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.

Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.

Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.

On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.

Putting it all together

On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.

You should have something similar to the below.

Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.

Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.

Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.

Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.

Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added

Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.

Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.

Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.

Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button

This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button

I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option

I then added a border to this option and positioned it (through trial and error) where I wanted it to be.

Adding the interactivity

The final step is to now add all the required dashboard action controls.

At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂

We need to set which date part has been selected on the control. This is a parameter action

Set Date Part

On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.

Add another parameter to capture which radio button has been selected

Select Relative Date Option

On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).

Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action

Deselect Dale Part Selecteor

on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared

Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.

And with all that, the dashboard should now be built and functioning as required.

My published solution is here.

Happy vizzin’!

Donna

Double drill-down with set actions

Donna ColesLeave a comment

Erica provided a set action based challenge this week requiring us to drill down to 2 levels in a hierarchy. I know there’s been similar challenges to this in the past, but I resisted checking them out, and attempted to build from memory. I succeeded – yay! Let’s crack on…

Note – I used the v2022.1 Superstore data that I already had on my laptop so filtered to December 2022. I also connected to the provided tds file rather than the excel instance, as for some reason, the tds includes Manufacturer which the excel file doesn’t.

Creating the main viz

On a new sheet, add Category to Rows and Sales to Columns and display as a bar chart. Add Order Date to Filter and restrict to the latest month/year in the data you have (in my case Dec 2022).

Create a set based off of Category (right click the Category pill > create set) and select a single value (eg Furniture) to be in the set. A field called Category Set should now exist in the data pane.

What we need to do is create a 2nd level in the hierarchy that shows the sub-categories related to the Category in the set, otherwise just display the Category.

2nd Level

IF [Category Set] THEN [Sub-Category] ELSE [Category] END

Add 2nd Level to Rows. Then right click on the Category Set field in the data pane and select Show Set to display the set values in a control on the sheet.

If you manually change the options in the Category Set control, the values displayed in the 2nd Level column will change.

Change the sort of the 2nd Level pill to sort by Sales descending

Create a set based off the 2nd Level field (right click field > create set), and select a single option that is one of the Furniture sub categories (eg Bookcases). A field called 2nd Level Set will be added to the data pane.

What we now need to do is create a 3rd level in the hierarchy that either shows the Category, the Sub-Category or the Manufacturer depending on the options selected in the sets. This is the only field which will actually be displayed on screen.

Display

IF [Category Set] AND NOT([2nd Level Set]) THEN ‘- ‘ + [Sub-Category]
ELSEIF [Category Set] AND [2nd Level Set] THEN ‘ — ‘ + [Manufacturer]
ELSE [Category] END

If we have a value selected in the Category Set but no value selected in the 2nd Level Set then display the Sub-Category (with some additional text formatting to give the indented effect).

Else, if we have a value selected in the Category Set and a value selected in the 2nd Level Set, then display the Manufacturer (again with additional text formatting and spacing).

Otherwise, display the Category.

Add Display onto the Rows, and then right click 2nd Level Set and select Show Set. Apply a sort to the Display pill so it’s also sorting by Sales descending.

Test the functionality, by changing the options in the set controls. For representative testing, ensure only 1 option maximum is selected in each set, and if an option in the 2nd Level Set is chosen, ensure it’s a Sub-Category related to the value selected in the Category Set.

Once happy, revert the options back to Furniture and Bookcases.

Format the sheet, and adjust the Row Divider so the slider is at the 2nd mark, and set the Column Divider to none.

Remove all gridlines. Then uncheck Show Header against the Category and 2nd Level pills in Rows. And finally, click on the Display title at the top of the column and Hide field labels for Rows.

To colour the bars, we’re going to use similar logic to that used to determine what to display

Colour Bar

IF [Category Set] AND NOT([2nd Level Set]) THEN ‘Sub Category’
ELSEIF [Category Set] AND [2nd Level Set] THEN ‘Manufacturer’
ELSE ‘Category’ END

Add this to the Colour shelf and adjust the colours. Update the tooltip and widen the rows slightly.

We’ve got the viz, now to add the interactivity so the changes happen ‘on click’ rather than via the set controls we’ve displayed.

Adding the interactivity

Create a dashboard and add the viz. Uncheck the options selected in both sets, so no options are selected – this should collapse the viz to just show the 3 Category values. Delete the container that shows the set controls/colour legend.

Create a new set action as below

Category Selected

on select (single selection only) assign the values to the Category Set, and remove all values when selection is cleared.

Create a 2nd set action

SubCat Selected

on select (single selection only) assign the values to the 2nd Level Set, and remove all values when selection is cleared.

Start clicking around, and BOOM! you should get the desired behaviour (note, once you reach the Manufacturer level, you’ll need to click twice on a manufacturer to get the viz to completely reset.

Bonus – building the ‘header’

I deduced from hovering over Erica’s solution that the ‘header’ was made up of multiple sheets, all aligned in a single row in the dashboard. I did have a play to see if I could come up with a single-sheet option, and while I think there is an alternative, it wouldn’t necessarily display exactly as Erica had shown. So I went down the multiple sheet option too.

Firstly we need a sheet to simply display the text >> Category.

On a new sheet, double click in the space below the Detail shelf on the marks card and type the text ‘dummy’. Then drag the ‘dummy’ pill onto the Label shelf, which will automatically change to now be labelled Text. Click on the Text shelf, and the 3dots to open the Edit Label dialog.

Delete the ‘dummy’ field, and replace it with ˃˃ Category. HOWEVER the ˃ symbol isn’t the > character I typed from keyboard. I found the double >> wasn’t retained this way, which I’m putting down to some HTML/XML related encoding that isn’t being handled. Instead I copied the symbol from this page https://www.compart.com/en/unicode/U+02C3 and pasted it into the text window. I then formatted the font size and style and coloured it the relevant blue.

Next we want to create a sheet showing the Category selected. Click on your viz to add a Category to the set (eg click the Furniture bar).

On a new sheet, add Category Set to the Filter shelf (this by default filter to values IN the set). Then add Category to Text and update the Text field as below, adjusting the font size/colour etc as before.

Duplicate this sheet as we can use this as the basis of the >>Sub-Category display.

On the duplicated sheet, update the text field to contain ˃˃ Sub-Category, where again the > is the character pasted. Format/colour the text

Now click on your viz to add a Sub-Category into the 2nd Level Set (eg click the Chairs bar).

On a new sheet, add 2nd Level Set to the Filter shelf (this by default filter to values IN the set). Then add 2nd Level to Text and update the Text field as below, adjusting the font size/colour etc as before.

Finally, once again, duplicate this sheet. Then on the duplicated sheet replace the text with >> Manufacturer instead

Now you have all the components you need. Add all these sheets onto your dashboard using a horizontal container to keep them all together. Apply the grey background shading if required (you’ll need to format the background colour on each worksheet, as well as the various objects on the dashboard.

My published viz is here. Enjoy!

Happy vizzin’!

Donna

How is the Arctic ice melt trending?

Donna Coles3 Comments

This week’s #WOW2022 challenge required us to build an horizon chart. While I’m familiar with them, I haven’t had to build one before.

Luke provided references to a workbook and blog post as part of the challenge. I was also aware that Marc Reid had recently published this blog post on the subject and that in turn referenced this Tableau guest blog post by Yves Fornes. Reading through them both I hoped I’d have the clues I’d need to build the chart.

Note – the blogs referenced all give a good explanation on the process we’re trying to achieve with the horizon chart (divide into equal sized layers and ‘merge’), so I’m not going to spend time repeating that in this post.

Unfortunately, despite a good start, I couldn’t just couldn’t quite manage it – I tried both Marc’s and Yves techniques and also downloaded Joe Mako’s workbook and tried to follow his method, but I just couldn’t get the display to match up, and there were minimal clues in Luke’s published solution.

My fellow #WOW participant Rosaria Guana also pinged me, as she had got further than I had, but wasn’t matching Luke’s solution either. With some assistance from Rosario I managed to build the solution that matched hers and seemed the closest fit to Luke’s. As yet we can’t figure out where the discrepancy may lie… maybe we’ve overthought the issue… who knows.

For now though, I’ll step through the solution as per my published workbook.

Sorting out the dates

The data needs to be segregated into decades, so first I created

Decade

IF [Year]>=1990 AND [Year]<2000 THEN ‘1990s’

ELSEIF [Year]>=2000 AND [Year]<2010 THEN ‘2000s’

ELSEIF [Year]>=2010 AND [Year]<2020 THEN ‘2010s’

ELSEIF [Year]>=2020 THEN ‘2020s’

ELSE NULL
END

In the requirements, it mentions using data from the 1980s onwards, but the solution only presents 1990s onwards. I interpreted this as a typo in the requirements, so I added a Data Source Filter (right click data source > Edit Data Source Filters), and added Decade excludes Null to eliminate all the unnecessary rows.

The chart shows 10 years worth of data at the day level across the x-axis. We can’t just use the existing Date field to plot as this will extend for 30+ years. Instead we need to ‘baseline’ the dates to a fixed set of 10 years.

Firstly we need to know what year in the decade it is (ie the 1st, 2nd, 3rd year etc).

Year Index

RIGHT(STR([Year]),1)

This returns values of 0, 1, 2, 3… or 9

I then create the years I’ll baseline my dates against – this can start at any year. I chose 1980.

Baseline Year

INT(STR(198) + [Year Index])

So this will result in years 1980, 1981, 1982… up to 1989.

I can then create a new date which I’ll use to plot against

Baseline Date

MAKEDATE([Baseline Year], MONTH([Date]), DAY([Date]))

If we pop some of this data into a table, you can see how the Date field relates to this revised date

The 1st of January for the 1st year in each decade will all be plotted against 1st Jan 1980 etc.

Normalising the Arctic ice (NH) value

The NH values need to be adjusted so that they all sit on a scale of 0-1. This means the date with the lowest NH value across all the years will have a value of 0, and the date with the highest NH value across all the years will have a value of 1, and all the values in between will sit proportionally between. To compute this we need

Max NH

{FIXED : MAX([NH])}

Min NH

{FIXED : MIN([NH])}

Normalise NH

(AVG(NH) – SUM([Min NH])) / (SUM([Max NH]) – SUM([Min NH]))

take the difference between the NH value and the minimum as a proportion of the difference between the max and min values. This gives us the scale we need – the image below is sorted based on the Normalise NH value descending (so the dates aren’t in order)

Creating the Levels & the chart

All of the above I managed with no issue. It was this section that I struggled with 😦 As mentioned I tried all sorts of calculations, and after discussions with Rosario, the calcs I was using based on Yves blog post came closest…

We’re aiming to get bands which are 20% (0.2) in width.

Range 0.8-1

IF ([Normalise NH]) > 1 THEN 0.2
ELSE
IF ([Normalise NH])<0.8 THEN NULL
ELSE ([Normalise NH]) – 0.8
END
END

If the normalised value is over 1 then draw a mark at 0.2, our band size (there won’t be any values, but I did this for completeness). Otherwise if we’re below our lower threshold of 0.8, don’t draw anything, else as we’re within our specific range, draw a mark at the point which is the difference between our normalised value and our lower threshold of 0.8 ( ie a record with a normalised value of 0.91 will plot at 0.11).

On a new sheet add Baseline Date as a continuous exact date to Columns and Decade to Rows. Then add Range 0.8-1 to Rows too. Change to an Area chart.

Now let’s build the next level based on the same principals

Range 0.6-0.8

IF ([Normalise NH]) > 0.8 THEN 0.2
ELSE
IF ([Normalise NH])<0.6 THEN NULL
ELSE ([Normalise NH]) – 0.6
END
END

As you can see this is very similar to the above calculation, but this time, we will have values greater than the upper threshold of 0.8, which will all be plotted at 0.2.

Drag this field on the Range 0.8-1 axis and drop when you see the ‘two column’ symbol appear. This will make the chart a Combined Axis chart and Measure Names and Measure Values will automatically get added to the view. Colour the Measure Names accordingly, and up the transparency on the Colour shelf to 100%.

This looks very close to what we need, but you can see the axis is plotting values at >0.2. This is because the measures are stacked on top of each other. We need to turn this off via the Analysis > Stack Marks > Off menu.

When you do this, it’ll look like some marks have disappeared, but they are just hidden behind the other marks. Use the Measure Names colour legend on the right to drag and re-order the options listed.

Let’s now add the next level

Range 0.4-0.6

IF ([Normalise NH]) > 0.6 THEN 0.2
ELSE
IF ([Normalise NH])<0.4 THEN NULL
ELSE ([Normalise NH]) – 0.4
END
END

And drag the field into the Measure Values box. For the purposes of display at this point, I have coloured this measure a light grey, when it should be white (but that won’t show up at this point).

Now if we continue with the pattern and create similar calculated field for the next 2 ranges, we end up with something that isn’t right… too much red, and white in places we shouldn’t have white… 😦

This is where I started having discussions with Rosario. I could see by observing Luke’s solution that the dark reds were ‘inverted’ ie looked like humps coming ‘down’ from the top line, rather than humps ‘going up’ from the bottom line, but I was stumped where I needed to go next. Rosario confirmed this was her thinking too, and provided alternative computations for the light and dark red levels.

Range 0.2-0.4

IF [Normalise NH] < 0.2 THEN -0.2
ELSE
IF [Normalise NH]<0.4 THEN [Normalise NH] – 0.4
ELSE NULL
END
END

If we’re below the lower threshold, plot at -0.2 (minus 0.2) instead. If we’re within the lower and higher threshold, plot the difference between the Normalise NH value and 0.4 (which will be a negative number), otherwise plot nothing.

and finally for the last band

Range 0-0.2

IF ([Normalise NH]) < 0.2 THEN [Normalise NH]-0.2
ELSE NULL
END

Add this to the view, but you’ll need to change the order, so this measure is sitting above the Range 0.2-0.4 one.

Ok, so the shapes look like they might be correct, although we are missing some dark red in the 1990s compared to Luke’s solution, and as mentioned at the start, we don’t know where the discrepancy comes from.

So how do we make this all be ‘above the line’.

Drag another instance of Measure Values onto Rows so it’s sitting next to the existing one.

The make the chart dual axis BUT DON’T synchronise axis.

Instead, right click on the left axis to edit it, and fix the axis from 0-0.2

Then right click on the right axis to edit, and fix this from -0,2 to 0.

Set the colour of the Range 0.4-0.6 back to white, and you should have

Now you just need to

  • add tooltips
  • Edit the date axis and fix from 01/01/1980 to 31/12/1989
  • hide all 3 axis
  • remove row gridlines
  • remove column dividers
  • hide the null indicator
  • hide field labels for rows
  • adjust the font side and alignment of the Decade header

My published viz is here.

A few days later…

Since authoring this blog, Luke has made his workbook available to download. I have looked at it, and the calculations he uses are much much simpler. However I’m still struggling with the concept and not fully convinced with it all. I have played around with the workbook, and try to step through based on how the horizon chart is described in the various blogs, but I’m not entirely sure I know what the right answer might be in order to know whether what I’m seeing is actually correct or not. Feels like this is one I could do with a face to face discussion over, and ultimately is probably one for another day. ..

Happy vizzin’!

Donna