Table Calcs vs. LOD
Understand LODs, Table Calcs and when to use which
As lot of you might already have experienced working with data – there’s a lot of ways to reach your numbers, but not all are equally effective. This blogpost can hopefully give you some guidelines not only for reaching your numbers but also doing it smart.First, the blogpost will explain in short what LODs, Table Calcs and Order of Operations is about, so if you already know the basics of these you can jump dirrectly to the end of the post to learn about considerations of when to use LODs or Table Calcs.
Datacated to the numbers
Below we see a table with five different variables all showing the same numbers. Everything in red is calculated by LOD, blue is a Table Calc (window_sum) and green is simply the data source sum(sales). EASY!
But what happens when we start filtering…
We see that the fixed calculation stays the same no matter what is selected in the filter while all of the other sales calculations change values. From this we learn one of the most important consideration choosing between Table Calcs and LOD - namely filtering. We will return to this learning in just a moment.Let’s first take a step back and try to understand the basics of LODs and Table Calcs!
LODS of possibilities with LOD
Using LOD’s It’s important to understand: What is your viz’s level of detail? When you drag a dimension out to either rows, columns, color, size, label or detail it will add to the level of detail of the viz (Dragging a dimension to either tooltips or filters will not affect the level of detail). Tableau aggregation will automatically adjust to the viz level of detail. I can think of several use cases where I want my numbers to stay independent of the viz level of detail – as for example adding the country total sales to a map at the state level. This is where LOD’s comes in handy!
Best Practice working with LODs:
Verify your numbers! It’s very easy to build a view with the level of detail that you are trying to get in your LOD calculation and verify if it gives you the same numbers. In the simple example below a fixed calculation get me sales pr. category (fixed is needed since the viz level of detail is sub-category). I test the numbers by creating a simple table which actually got the category as viz level of detail and see that the numbers are the same. Happy times!
Around the Table (Calc)
Table Calcs gives you a fast way to create advanced calculations without having to do long complicated syntax. It’s a calculation created on the results of your viz which gives possibilities for row level calculations and second aggregations. The hard thing about table calcs can be to define the correct scope, direction and ’compute using’.
Best Practice working with Table Calcs:Start by building your table calc in a classic cross tab since it is easier to verify if ’compute using’ is working as we want. The build in highlight function is very useful when you change the ’compute using’. Afterwards the table can be used to verify our numbers as we use the table calc in the specific viz we want to create. In fact, we can use the ’specific dimensions’ box by simply clicking it when we found the wanted ‘compute using’ and it will lock down the numbers that will stay the same even when we start moving around our dimensions.
One very important feature to consider when choosing between LOD and Table calcs is as we learnt in the beginning: filters! To get a better understanding of this – we need to have a look at the Order Of Operations (OOO) also called the query pipeline of Tableau. From this we learn that Table calcs are at the end of the query pipeline, why these are affected by filtering. Table calcs are actually calculated in the view after the result query is send from the database back to Tableau.
From OOO we observe that fixed calculations ignore all filters in the view except context filters, data source filters and extract filters. Include and exclude do not ignore the filters in the view. All confirming what we saw in the beginning.
Considerations using LOD or Table Calcs?
Recursive calcs, ranking, moving calcs: TABLE CALCs is the only way!
Performance: LODs can be heavy so if performance is slow replace with a Table Calc (if possible) since Table Calcs are not executed in the database (as LOD) but by tableau on database query results.
If you don’t want your number to change with your filter, ex. % of total to stay constant: Use fixed!
Flexibility: Table Calcs are often more flexible since you only need to specify ‘compute using’ while LODs needs to have specified all the specific dimensions necessaire and you need to address all the dimensions in the viz level of detail when doing include and exclude calcs.
Thinking about the viz level of detail: Table calcs can only produce results that are equal to or less detailed than the viz level of detail, were LOD can create results that are independent, more or less detailed.
Result from calcs: Table calcs will result in aggregated measures while LODs can result in measures, aggregated measures or dimensions depending on what you type in you calc.