Versions Compared

Old Version 23

changes.mady.by.user Steve Martin

Saved on

compared with

New Version Current

changes.mady.by.user Steve Martin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Beginning with version 9 in March 2015, Tableau rolled out its then newest calculation feature - Level of detail expressions, an all-new powerful extension set that enables developers to provide an extra-level of detail to their visuaisations visualisations that crucially, could be unbound from the framework of the existing view - or rather, could be calculated on an altogether dfferent set altogether different set of dimensions, yet incorporated into the current visualisation.

Table of Contents

Syntax & Scope

Like table calculations, Level-of-detail expressions (LOD ExperessionsLOD Expressions) need to be scoped, but unlike table calcs which can consider cell, panes and windows, lod's need to be scoped to one or more dimensions, furthermore, the scope is defined in the calculation, and so persists for all calculations.

Syntax:

The syntax to initiate an lod remains the same regardless of scope, and, just like a regular table calculation, you are free to pre-determine the aggregation used, or, to allow Tableau to apply the default calculation (typically Sum), as you apply the calculation. The one caveat is that any measures used in the calculation must be aggregated:

...

This will calculate the percentage difference between the Sales and Profit for each category member (Superstore: Furniture, Office Supplies, Technology)

Scope:

Four types of scope exist for level-of-detail expressions:

Fixed (Fixed grain)Include (Lower Grain)Exclude (Higher Grain)None (Highest Grain)

The Fixed-type caluclates calculates only on the specified dimension(s)


Code Block
languagesql
{Fixed Region : Sum(Sales)}


Include aggregates across all the dimensions of the view and those of the expression, as a result, the effect of the Include-type will only be noticed when not used with sum.

Code Block
languagesql
{Include Segment : Sum(Sales)}


The polar opposite to Include-scope, Exclude removes the named dimensions from the final result so calculates at a higher-grain than may be available in the given view


Code Block
languagesql
{Exclude Category : Sum(Sales)}


The highest-grain of all, where no scope (and therefore dimensions) are defined, the result is always the highest value of the resultsetresult set


Code Block
languagesql
{Sum(Sales)}


Notice the difference here: Using include with a summation aggregation is functionally the same as a standard sum, only when the aggregation is set as Avg do do we see a difference

Architecture

This is the tech-part, lets look at how level-of-detail expressions work, behind the scenes.

Consider this view:This is the SQL required to generate the viewAnd this is the plan with some of the key measurements highlighted


Code Block
languagesql
Select
Region
,Segment
,Sum(Sales) As Sales

From Superstore
Where Category In('Office Supplies', 'Technology')

Group By Region, Segment


Are LOD Expressions the Only Option?

Let us now consider a second view, this one uses the same Sum calculation, a Fixed LOD calculation and finally, a Window_Sum:

...

I think we can agree that for such a tiny table, the code is quite extensive; but did you notice that both the Fixed and the WIndowWindow_Sum are calculating the same output.

...

Expand
titleAnswer...

Yup, the Window_Sum. Why?

Well, because the windowWindow_sum Sum is nothing more than a straightforward aggregation, the windowing function is applied inside Tableau.

Note

But, despite the clear perfromance performance drawback to Level-of-detail expressions, you must always consider your use case, as Window Calculations still need your data to structured such that the scope can be applied, whereas LOD expressions, particularly Fixed expressions or Min/Avg/Max aggregations, give you power to provide additional information without a supporting structure.

Furthermore, it is possible to include lod expressions inside a window calc if needed, this is particularly useful when performing lookups and ranking operations.


Concluding Remarks

It is plain to see that Level-of-detail expressions are a very valuable tool to include in your development arsenal, that said though, as with all features with Tableau, developers need to consider whether their use is justified in the view they are currently building.

It is clear from the demonstrations above that alternatives do exist, and where possible, these alternatives should be explored, especially when connecting Tableau to large datasets in excess of 1 million rows, as the impact of these overly costly querie query will greatly impact time-to-render, and may also impact other resources.

...