Understanding Level of Detail Expressions – Part 2

In the last post I explained how the three types of LOD expressions work. Clearly they make working with multiple levels of aggregation much easier, but we could solve these problems in the past using techniques like table calculations and blending. So how do the two approaches compare?

EXCLUDE

EXCLUDE expressions create results that are (more aggregated/less granular) than the viz LOD. Previously we often used table calculations to do the same because they are aggregated from the query results. Note that table calculations can only work “up” from the Viz LOD (i.e. more aggregated/less granular).

The addressing fields are the dimensions that would be excluded in the LOD expression.

The equivalent of an EXCLUDE expression could also be achieved by blending. You blend the data source against a copy of itself and explicitly unlink the dimension to be excluded.

The unlinked fields are the dimensions that would be excluded in the LOD calculation.

The equivalent of an EXCLUDE expression can also be achieved with subtotals and reference lines – however these are purely visual constructs and cannot be used in calculations.

INCLUDE

To achieve the equivalent of an INCLUDE expression you need to change the Viz LOD and make the query results (less aggregated/more granular).

You start by making the Viz LOD (less aggregated/more granular/lower).

You then use table calculations to aggregate back up to the desired LOD for display. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.

Once again, you could also use an unlinked self-blend to roll up from the new Viz LOD to the required display LOD. Same as before, y start by making the Viz LOD (less aggregated/more granular/lower).

The value from the blended data source is then used to show the original Viz LOD. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.

FIXED

What about FIXED expressions, I hear you ask? Well, before LOD expressions creating an aggregation completely independent of the Viz LOD was only possible with custom SQL.

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.

4 Responses to Understanding Level of Detail Expressions – Part 2

  1. Ankit Suda says:

    Hi Alan, I have seen cases where dimensions are not used while created calculated fields using LOD expressions. In my case wherein INCLUDE is used but dimension argument(s) are blank in the calculated field.

    • Hi Ankit, it is valid to use a FIXED LOD without a dimension specification. It means to perform the operation over all the data. So {FIXED : MIN([Order Date])} would return the date of the first order in the whole data set. Using INCLUDE or EXCLUDE without a dimension I haven’t seen before as logically it would be the same level of detail as the viz.

      • Ankit Suda says:

        Hi Alan,
        I have downloaded a sample workbook from one of the tableau websites. The following is the URL “http://www.tableau.com/LOD-expressions”. Here in the 9th Example, for the calculated fields INCLUDE has been used without dimension.

        I know without the dimension the Level of Details is same as the viz. I did some tests/checks on to make it sure as well.

        But still I need expert opinions on this. Would you mind to have a look on the above mentioned example as well.

        Thanks for your reply and time.
        Ankit Suda.

  2. OK – so the reason Bethany is creating the LOD in that example is to have the MAX(Order Date) calculation occur on each row. It’s the kind of calculation you would previously have done with a WINDOW_MAX() table calculation, but in order for that to work you would need to change the granularity of the viz to include the day level data. Note that Bethany’s viz is using the MONTH() level of aggregation on the columns.

    Because LOD calculations are done for each row and are done as part of the query, Bethany doesn’t need to return the day level of data to drive the table calculation. Rather she can have the equivalent value calculated as part of the LOD and then each detail record will have that value in a column. She then uses it in the comparison test to only return the value observed on the last day of the month.

    Very clever. I hadn’t thought to do that before.

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