Benchmarking using LOD's (replicating values, freezing values, sub-queries)

A great use case for Level Of Detail expressions in Tableau is to do benchmarking or comparisons.  The scenario is that you have some Dimension column with a bunch of values in, and you want to pick one of the values and 'freeze' or 'replicate' those numbers so that no matter what you filter on, they will still be available to make comparisons.

For instance, say you have a dataset with competitor data in it.  Your company is one of those competitors, but you also have a bunch of market data for the others.  A common thing you might want to do is to compare your company's performance to a specific competitor.  The challenge with this is that if you add a filter to just keep the data for one competitor, then your company's data will be filtered out too, so there is nothing to compare to anymore!  What would fix this is if we were able to 'freeze' our company's numbers so that no matter what we filter on we will always have our company's data available to make comparisons to.

LOD expressions offer an elegant way to achieve this (with no data modeling necessary!). Essentially we can 'fix' or 'freeze' our company's numbers using the FIXED type of LOD expression.  Because of the way Tableau's query pipeline works, any calculations that use FIXED will happen before most other filters are applied.  We can use this to our advantage by fixing our numbers so that when we filter on a single competitor, the math done to compute our company's numbers has already been done and is available for all the other rows of data.  See the below image and notice how Dimension Filters happen after Fixed LOD's are calculated.

http://onlinehelp.tableau.com/current/pro/online/windows/en-us/calculations_calculatedfields_lod_filters.html

Therefore you can filter on any competitor you want and still be able to compare them to your company's numbers.

First let's write a Fixed expression that pre-calculates only the numbers from our company (in the example our company is called 'JM Gebs').  We will find our company's rows with an IF statement, then sum up those rows, and Fix those numbers at the entire-dataset-level:

See!  It works!  Our company's numbers are replicated for all the other companies too!

  

And it sill works even if we filter us out!  Booyah!

Something to keep in mind when using Fixed calculations is that you need to specify any dimensions you want to group those numbers by (for those familiar with SQL, you need to specify the GROUP BY part of the query).  For example, if I want to build a view where I show the comparison at a Country level, then I need to specify in my calculated field to fix/group the math at the Country level:

See now the numbers are different per Country

A consequence of this is that you may need to create many versions of this LOD calc depending on what dimensions you are using in your chart.  You might have a dashboard in which you have a monthly trend of the comparison, a comparison by country, another by segment, etc, so you will need a separate LOD calc for each one of these since they will each need to have different dimensions on the left side of the calculated field (each chart will need the math to be 'grouped by' different dimensions).  Your other option is you can make one calculated field with all the dimensions you want to group by in it (just comma-separate them) - this will work so long as your numbers are additive by nature - i.e. this will not work for something like Unique Customers (which would use CountD, count distinct) - I like just making separate ones for each so I am 100% sure the math is working correctly.

Another consequence of the order of how things are applied - that normal filters (by normal I mean stuff you just add to the filter shelf and dont do anything special to it like make it a Context Filter or make it a Data Source Filter) are applied after the Fixed calculation math happens - is that most other filters you want to add should probably happen before the numbers are 'fixed/frozen', therefore you need to make them Context Filters!  This is not a bad thing at all, but it is just something to be aware of.  I find that I add pretty much every filter besides the one on Company (or whatever you are doing the benchmarking against) as a context filter.

For example, say we have a chart where we are doing the comparison per Country, but we want to let the user filter to a certain Segment (or Year or anything else not being used in the view).  We really want to apply that Segment filter before we pre-calculate our company's fixed sales numbers, because we want to see our sales number just in that Segment.  If we dont make the Segment filter a Context Filter, the math will happen across all Segments and then it will just filter out the rows of data after the fact (which often has no effect, at least not the desired effect).  Again, reference the image above which shows the query pipeline order.

This also applies to Action Filters. For example, if you have a dashboard with two views - a monthly comparison and a comparison by country - and we want to allow users to click on a country and filter the other chart (and vice versa, click a month and filter the countries to just that month) then we would use a Filter Action to do that.  Great! but.... remember that the action filter is going to be applied after the math has already been calculated.  What we really want to happen is if I click on one month, I want to see the country breakdown just for that month.  Therefore in order to make that action filter apply before any of the fixed calculations, we need to add it as a Context Filter.

That should get us the desired behavior.  Now we are able to pick any competitor we want to benchmark ourselves against, and we can use any quickfilters we want to limit by Segment etc, and we are able to use our Filter Actions to drill from one chart to the other on the dashboard, all while being able to keep our number's available for performance comparisons - pretty cool!

-The Don

Workbook:  https://app.box.com/s/pnm61jivsaym5ztrjoeztqlornhjfi3u

*Note - you can set the Competitor quickfilter to multi-select, too.  I just happened to pick single-select (this is one reason why this is better than using parameters to achieve this)

*Note - if you want to show a quickfilter to pick different competitors, but you dont want to include your own company in the list, you can create a Set from the Company field and exclude 'JM Gebs', then show the quickfilter on the Set instead.