11 Replies Latest reply on Dec 26, 2016 10:25 AM by Sreehari Katageri

# LOD

Hi Tableau Experts ,

I am confused with the LOD concepts , I know hoe to use LOD ( Fixed , include , exclude ) , gone through some example given in the web that LOD defines  the scope , defines  the aggregation etc , but I am confused that

when should we use LOD , in what scenario ?

Please can anyone explain I will be thaknful & will get clarity on LOD

• ###### 1. Re: LOD

hi Rahul,

So this is a big question!...but in short when you use AGGREGATED calculations in Tableau, such as SUM([Sales]), AVG([Sales])...etc. the results are dependent on the Level of Detail in your Viz (known as VizLoD). So if you have (from Superstore) SUM([Sales]), and have no dimensions in the Viz, the result will be the Total Sum of the Sales of the data set, if you now bring in 'Region' to the Row Shelf, the Sum of Sales is now shown for each region. LoDs allow you to calculated the aggregate at a different level to that of the VizLoD.

The below article gives 15 examples of LoDs, and shows how they work and why they are different from using a regular Aggregated calculation

Top 15 LOD Expressions | Tableau Software

I've also attached an example I use in my Training, to show a use-case for FIXED LoD for customer segmentation...Let me know if this doesn't make sense, and I'll go into a bit more detail.

• ###### 2. Re: LOD

Hi Rahul,

I had the same question when i started using LOD. The only way i can decide on when to use LOD is when i have to calculate the values on a measure different then the ones in the rows, columns or details shelf. Lets say you have County, State, City and Population in your dataset. You have brought in City to rows shelf and showing measure value of population against it. But now you also want to see what is the overall state population for each of the city. You may use FIXED in this case to sum the values of population on State and use that as well into measure values display. Now you will have 2 values one is the city's value and the other is the State's overall population value. The State's overall population values will be repeated for each of the cities of the same state.

Additionally you may think of it as showing the values one level higher then in your shelf. That's the most common scenario where i use it.

-Amanjot

• ###### 3. Re: LOD

Hi Rahul - great question! As Tableau's feature set continues to grow, and each of these functionalities is improved, there is more and more overlap between functionalities. This is a good thing! Having options and being able to use whichever option makes the most sense or the one you're most familiar with is a luxury!

That being said, when teaching this subject, I usually say the following as an overview of LoDs (I realize it's a bit of an oversimplification): LoD calculations are for when you want to compute something at ANY level which is not row level or the level in your view.

Put another way, if you want to compute aggregates (SUM/AVG/COUNT/COUNTD/MEDIAN/ETC) at ANY level which is not at the level of the dimensions you have in your current sheet, or the most granular, row level, the only way to do this is through LoDs. Again - there are a lot of other things that LoD calcs can do (unfiltered numbers, totals for use as a demoninator/% of total, etc.), but these are generally possible via some combination of table calculations/sets/additional detail.

1 of 2 people found this helpful
• ###### 4. Re: LOD

Hi Rahul Panday ,

Please follow this workbook it will help you to get an insight when LOD need to be used. i got this from community it helped me a lot.

• ###### 5. Re: LOD

Hello Rahul,

Here is a great thread (With some useful links and awesome comments) on the topic of LoDs, how they work, and when to use which.

Should I use FIXED or INCLUDE/EXCLUDE?

Be sure to check out the Master of LoDs (Jonathan Drummey) comments, as well as, the links that are provided throughout.

Best regards,

Rody

1 of 1 people found this helpful
• ###### 6. Re: LOD

Thanks for the compliment, Rody!

Here's where I'm currently at in my own thinking, I don't have absolutes, just certain leanings. To define a couple of terms:

Computation grain:

- If the desired computation results are coarser than the vizLOD (such as a window average) then I'll lean towards using a table calc.

- If the desired results depend on a finer grain than the vizLOD (such as returning the earliest admission date per patient) then I'll lean towards using a regular aggregate (potentially with an embedded record-level calculation) or an LOD expression.

Mark to mark comparison/iteration (running totals, lookup, ranking, etc.)

- LOD expressions do not currently support moving totals, running totals, ordinal computations within a partition (LOOKUP, INDEX, etc.), ranking, or anything like PREVIOUS_VALUE so if we need those kinds of computations then we almost certainly need to use table calcs. (Though there are occasional tricks to get around some of these with LODs depending on the data and the exact calculation needed, for example [value] = {FIXED [dimension], MAX([value])} to identify the top/1st value in each partition).

Re-using computation results:

- If I'm looking to re-use the computation results as a dimension then I'm almost certainly going to use a FIXED LOD. While there are techniques for using table calcs they can get hairy-complicated very quickly.

- On the other hand, if I'm doing something like variance from average then I'll tend to use table calculations first because they are quick to set up (see my comments in the thread that Rody referenced) and computed locally (see below for details).

Data volume vs. # of marks:

- If there are large data volumes and relatively few marks then I'll lean towards using table calculations because LOD expressions generate subqueries that can have a big performance impact while table calculations are computed locally only across the marks in the view.

Performance & cohort-building:

- As much as FIXED LOD expressions are fantastic at building cohorts the extra queries they add can cause unacceptable performance overhead. I still need to use custom views/queries at times to do the cohort computation outside of Tableau.

Data quality:

There have been a few cases where FIXED LOD expressions have saved the day by being able to de-duplicate data sets or assign a single text descriptor to an ID that has multiple text descriptors due to the values slowly changing over time. This is something that table calcs aren't suited for.

Quantity of filters:

- If the view has a large number of filters in play then I lean towards using EXCLUDE LOD expressions and/or table calculations since they are computed later in the pipeline. Since FIXED LOD expressions require context filters to affect them that can a) increase the time it takes to build a view (since all regular filters need to be turned into context filters), b) slow the view down, c) make the view harder to maintain (since every new dimension filter probably needs to be added to the context), and d) reduces the options available to me since I'm using up context filters to filter the FIXED LOD expressions.

Results unaffected by filters:

- There are several options to get a computation result unaffected by filters on the view:

- FIXED LOD expressions, computed Sets - affected by Context filters, unaffected by regular dimension filters & Filter Actions

- table calcs - affected by context & dimension filters, unaffected by table calculation filters

- data blends - lots of dependencies here on grain of data, grain of blend, vizLOD, style of blending, and filters; with those caveats it can be possible to get computations outside the vizLOD that aren't affected by filters.

Custom Grand Totals/Sub Totals:

- If the view is using custom grand totals that aren't supported by the existing two-pass/visual grand totals then I look at using LOD expressions first as a possibility (given other constraints such as the kind of computation) because they can give us two levels of aggregation without the complexities of table calculations.

Using computation results as a target of Filter Actions:

- While most any calculation can be the origin of a Filter Action, only dimensions can be the targets of Filter Actions so we need FIXED LODs for that.

Tableau data blends:

- When using data blending depending on the particular blend there are limitations as to what we can do with LOD expressions. For example if I'm using a FIXED LOD expression within a secondary data source then I can't pre-filter the data because we can't add fields from the secondary as context filters. So sometimes we need to resort to table calculations.

Data source:

- Not all data sources support LOD expressions, *every* Tableau data source supports table calcs.

Densified views:

- Since LOD expressions are computed in the data source they are computed prior to densification. If we want an LOD expression-type computation to happen after densification then we have to use a table calculation, not an LOD.

Bugginess:

- LOD expressions were introduced in v9.0 and still have some rough edges. I've had problems with nested LODs, LODs blanking out a view, and found a couple of other bugs. Table calcs have been around for awhile and although they are much more complicated to set up and maintain than I'd like I haven't had a crash due to them in years (excluding densification behaviors that cause out of memory issues).

Jonathan

14 of 14 people found this helpful
• ###### 7. Re: LOD

Wow, yup that just got bookmarked

Like I said, The Master!

Thanks Jonathan Drummey

Regards,

Rody

1 of 1 people found this helpful
• ###### 8. Re: LOD

Thank you guys for your valuable posts .

On Tue, Feb 16, 2016 at 9:50 PM, Rody Zakovich <tableaucommunity@tableau.com

• ###### 9. Re: LOD

Jonathan Drummey this is a valuable resource, and is being shared internally to help users understand when LODs are appropriate -- thanks for posting!

• ###### 10. Re: LOD

Aww, thanks, Matt!

Jonathan