It's all in the Details

by Caitlin Walsh

LODs are very useful, but not always very intuitive at first...
For my own benefit (and maybe yours?) I am noting down a way to use LODs to highlight certain months from within a dataset, in a way that is more future-proof and dynamic than simply hard-coding them.


LODs can perform similar tasks to a Table Calculation, but with advantage of letting the user specify how a value is aggregated; it will not be affected by what we bring into the view.

I have a bar chart showing Superstore data, breaking down continuous Monthly sales by Region:

My goal here is to use colour to mark out some key months in this dataset: the Latest Month, Prior Month, the Month exactly one year before the Latest, and the very First Month.

We can start off fairly simple, and create an LOD to return the Latest Month in the dataset:

{ FIXED [Region] : MAX( DATETRUNC('month', [Order Date]) ) }

In plain English: we are asking Tableau to return the month of the latest possible order date, for each Region.
Because it will always look for the MAX Order Date, this calculation will always return the very latest month in the data, even if it is extended in the future to include more months.

We find the First Month in pretty much the same way, except now we find the MIN Order Date:

{ FIXED [Region] : MIN(DATETRUNC('month', [Order Date])) }

Again, in plain English: this time we are asking Tableau to return the month of the earliest possible order date, for each Region.

We can now start building out a calculated field to colour in the month bars if they meet the criteria for First and Last; while we're at it, we can also add some conditions to colour in the Prior Month and the 12th Month Prior, since these are relative to our 'Latest' LOD:

IF DATETRUNC('month', [Order Date]) = [Latest] THEN 'Latest'
ELSEIF DATETRUNC('month', [Order Date]) = [First] THEN 'First'
ELSEIF DATEDIFF('month',DATETRUNC('month', [Order Date]),[Latest])=1 THEN 'Prior'
ELSEIF DATEDIFF('month',DATETRUNC('month', [Order Date]),[Latest])=12 THEN '12 Months'
ELSE ''
END

Here, in plain English:
For the First and Latest Month, we will label a month bar 'First' or 'Latest' if it matches the month from our LODs.
For the Prior Month, we will label a month bar 'Prior' if it has a difference of 1 from Latest Month, i.e. it is 1 month earlier.
For the 12th Month Prior, we will label a month bar '12 Months Ago' if it has a difference of 12 from Latest Month, i.e. it is 12 months earlier.

We can now add this new calculated field to the Colour marks card, and voila:

The Latest Month, Prior Month, the Month one year before the Latest, and the First Month in Superstore are all highlighted and distinguished from the rest of the months in the chart.

If for instance, we added new data for all Regions except East, we would see the colouring of our Central, West and South bar colours update to include the latest months; this is the purpose of fixing on Region.

For some really helpful resources on LODs, see Andy K's blog on Fixed LODs, and this mega post from Tableau.