Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

Need help using LOD’s – 

 

Think of your data set as a pyramid.  The data you upload creates the base of the pyramid – the top is the sum of all the data.  LOD expressions give you a way to create layers in your data and use those layers to categorize data into dynamic groups, sets, or ways to work around the limitations of table calculations – 
 
 
 

LOD Syntax

 
LODs use a keyword – Fixed, Include, or Exclude – followed by a list of dimensions that set the level of the virtual layer you want to create in the data set –  For example
 
                                {Fixed  [Segment], [Category]    : …

In words it says “make all the combinations of Segment (3 in Superstore data)  and Category (3) ”  and create a layer that consists of 9 total values – then save them so they can be used later
 
The colon :   is the divider that ends the layer definition and starts the formula of the value to save –
 
                                {Fixed  [Segment], [Category]    :   sum(Sales)} 

The result is a new layer in the data at the Segment/Category level

 
 
 

 
 

Creating multiple layers 


Adding dimensions before the colon will define another layer in the data.  To create a new layer that includes Year level place the Year(order date) in the dimension list

{ FIXED [Segment],[Category],year([Order Date]): sum([Sales])}

Now in addition to layer at the Segment/Category level, there is a layer at the Segment/Category/Year level

 



Filtering and the Order of Operation


LOD expressions can be filtered but the results will be affected if dimensions placed in the Context Filter

 
First, use a simple filter on a Dimension in the LOD expression – ( Segment ). The results are filtered as you would expect – the Consumer Segment is filtered out of the data – Three of the 9 values of the Segment/Category are filtered out of the result
 
 
 
 
 
 

Context filters affect

 

Add a filter for a Dimension not in the LOD expression Region in the example – filter the Central region and the 9 values from the LOD are NOT changed –

 

 

 
 
 

Change the Region filter to Context – the results do change

 

 
 
 What's going on?  

 
 
Context filters are applied BEFORE the LOD calculation in the Order of Operation – if a dimension is placed in Context Tableau will apply the filter before the LOD is calculated –
 
In this case – the Central Region is filtered out before calculating the Segment/Category LOD
 
Dimension filters are applied AFTER the LOD is calculated – the results reflect the un-filtered Segment/Category LOD
 
 
 

Use in other calculations

 
LODs place a value that is aggregated in a new virtual layer in the data set, but they are NOT aggregates in themselves.   That’s powerful! 
 

Replacing Table calculations

 
A FAQ on the Forum is how to fix a table calculation – but they are built on the table that underlays each worksheet and can’t be carried from one sheet to another.  

LOD’s can be used to "Fix" some table calculations – for example, Percent of total 

The table calculation formula for percent of total used Total() and is then calculated down (in this example)
 
SUM([Sales]) / TOTAL(SUM([Sales]))
 
LOD’s can be used to replace the numerator and denominator
 
                sum({ FIXED [Segment],[Category]: sum([Sales])})/sum({ FIXED [Category]: sum([Sales])})
 
 
The LOD base Percent of Total and now Fixed at the Segment/Category level 
 

Nested LOD and Table Calculation


LODs can be also be nested directly in Table Calculations – This example dynamically ranks Sub-Category based on annual sales

The LOD below will total Sales at the Sub-Category/Year level
 
{ FIXED [Sub-Category],Year([Order Date]):sum([Sales])}
 
Nesting the LOD in a Ranking function (Table Calculation)  will sequence the categories descending each year
 
RANK_UNIQUE(sum([fixed annual category sales]),'desc')
 

And return this

 

 

LODs in conditional statements and grouping

 
Need to separate records into groups based on the value of a single dimension?
 
Use a conditional statement (  if…then  ) in the aggregation portion of the LOD  (after the :  )
This says “for each order and category combination – separate out the Furniture lines and group the remaining as “Other”
 
      { FIXED [Order ID],[Category] : min(if [Category]="Furniture" then [Category] else "Other" end  ) }
 
The LOD will categorize each Order/Category combination into one of 2 groups – you can use the LOD like any other dimension in your viz
 
 
 
 
 

Include and Exclude LODs

 

 

 



Where Fixed works above the dimension filters in the order of operations Include and Exclude work after the dimension have been applied
 
 
 
 
 

Using the same Segment/Category but adding the Include and Exclude version we can see the effect of the LOD

{ FIXED [Segment],[Category]: sum([Sales])}

{ EXCLUDE   [Segment],[Category]: sum([Sales])}

{ INCLUDE  [Segment],[Category]: sum([Sales])}

Include is applied after the dimension filter  Year(order date) is applied and sums at the  Segment/Category for each year

Exclude applies the totals after the Year(order date) is applied and sums across all the Segment/Categories at the year level

 

 
 
I hope this gives you a better idea of how LOD expressions give you the flexibility to add different levels in your data set.  They are a powerful tool that can be used in conjunction with conditional statements, table calculations or nested in other LODs.  

Have fun – the best way to learn more is to use them with some simple example 

A Tableau workbook with copies of the examples presented here can be found on my tableau public site   https://public.tableau.com/profile/jim.dehner#!/vizhome/LayeryourdatawithLODs/FixedSegCatyrsumsales

Jim
 
 
 

Leave a Reply

Your email address will not be published.