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