Understanding Level of Detail Expressions – Part 3

A key point users will need to come to terms with is that LOD expressions are not just a new form of table calculations. Technically they operate quite differently and although there is some overlap in the problems they can solve there is generally a clear line in determining which to use. The following table shows how the two types of calculation differ:

Table Calculations LOD Expressions
Table calculations are generated from the query results.

WINDOW_AVG(SUM([Sales]))

LOD expressions are generated as part of the query to the underlying data source. They are expressed as a nested select so are dependent on DBMS performance:

SELECT T1.[State],SUM(T2.[Sales per County, State])
FROM [DB Table] T1 INNER JOIN
(SELECT [State], [County],
AVG([Sales]) AS [Sales per County, State] FROM [DB Table] GROUP BY [State],[County]) T2
ON T1.[State] = T2.[State]
GROUP BY T1.[State]

Table calculations can only produce results that are less granular than the Viz LOD. LOD expressions can produce results that are more granular than, less granular than or totally independent of the Viz LOD.
Dimensions that control the operation of a table calculation are separate from the calculation syntax and specified in the “compute using” or “running along” menus. Dimensions that control the calculation for a LOD expression are embedded in the calculation syntax. For INCLUDE and EXCLUDE expressions these are relative to the Viz LOD, for FIXED expressions these are absolute.
Table calculations are always aggregated measures. Calculated fields with LOD expressions can be measures, aggregated measures or dimensions. INCLUDE/EXCLUDE expressions are always measures or aggregated measures. FIXED expressions are dimensions by default but can also be used as measures.
Table calculations cannot be used in other constructs. LOD expressions can be used in other constructs such as bins, groups, etc.
Filters on table calculations act as a hide – they do not remove records from the result set. Filters on LOD expressions act as an exclude – they remove records from the result set. Depending on whether the expression is FIXED or INCLUDE/EXCLUDE it is evaluated at different stages of the visualisation pipeline.
Advertisements

About Alan Eldridge

Hi. I'm Alan. By day I manage the sales consulting team for Tableau Software in Australia/New Zealand. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Understanding Level of Detail Expressions – Part 3

  1. vizpainter says:

    Very helpful! Thanks for compiling these comparisons.

  2. Vlad says:

    Great series Alan! Definitely helpful for me to wrap my mind around LoD expressions. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s