Aug 14 2018

The More Things Change…

Categories: Calculation Context, General, Report Techniques Dave Rathbun @ 7:53 am

…the more they stay the same.

I’ve been fairly quiet here for a long time. Part of the reason is I’ve been busy with family and work. My older son will be a senior in high school this year, and my younger is just starting driving lessons. But something happened a few weeks ago that made me want to write again.

At work I’m now overseeing the Tableau team. We still have a lot of Business Objects users, but Tableau has become the “flavor of the month” that everyone wants to use. Tableau certainly has the right idea in that their product works identical whether viewed on the web or via a mobile device, and their mapping options are really nice.

But at the core, it’s still a data presentation engine.

A few weeks back, I was trying to solve a problem that I knew how to solve in Web Intelligence. Specifically, I wanted to generate a grand total that would return the same value no matter where I dropped it on my dashboard. In Web Intelligence this is done via Calculation Context and it looks something like this:

=Sum([Revenue]) In Report

By adding the key words “In Report” I am telling the tool that I want to see the grand total. I’ve covered calculation context before (Calculation Context Part I: Overview) and it remains one of my more popular posts. It’s definitely the one that has the most people asking, “Where’s part 2?” Other calculation options in Web Intelligence include “ForEach” and “ForAll” to go along with “In” that I have already mentioned. Here are the definitions I wrote the first time around:

  • In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding / removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a #MULTIVALUE error is displayed.
  • ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block.
  • ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items.

Why bring this up again? Because Tableau has exactly the same options. :lol: Instead of Context they call their option Level of Detail. You activate a Level of Detail operation by using curly braces { } around your calculation. Suppose you want a grand total, it looks like this:


That is the equivalent of “In Report” for Web Intelligence. What about the others?

Tableau provides FIXED, INCLUDE, and EXCLUDE as additional tags for their Level of Detail operations. Each of those is a direct equivalent to a Web Intelligence option.

FIXED is the equivalent of IN. These two statements are an equivalent way to request sales by year, even if other dimension values are present in the block or worksheet.

{FIXED [Year]: Sum([Revenue])}
=Sum([Revenue]) In [Year]

INCLUDE is the equivalent of ForEach. These statements are going to make sure the Year is included in the calculation, even if it’s not part of the displayed block or worksheet.

{INCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForEach ([Year])

Note that if there are multiple Years then Web Intelligence will show a #MULTIVALUE error, and Tableau will show a * as the result.

EXCLUDE is the match to ForAll. These formulas will specifically ignore the Year value when generating Total Revenue, even if it’s present in the block or worksheet.
{EXCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForAll ([Year])

The person who was starting to explain the Level of Detail function in Tableau started out by saying, “Now this is one of the more complex techniques that Tableau offers, so it may take a bit to get a handle on it.” As soon as he started the technical explanation I cut him off, saying, “Oh, it’s just like Calculation Context. I’m good.”


So yes, the more things change, the more they stay the same…

4 Responses to “The More Things Change…”

  1. Comment by Joe

    Thanks for this. I’ve been doing work with Tableau and was aware of those functions, but having the “mapping” to the syntax I know is helpful.

  2. Comment by James Halligan

    The new ‘Assign Reference’ functionality is going to help a lot of people circumvent/avoid the use of calculation contexts…

  3. Comment by Chandrasekahr

    Being ardent fan of BO i really like your punch line expression in the last paragraph…
    In addition, Tableau does not have “Change password at your first login option” while creating user.

  4. Comment by Mahboob Mohammed

    Hi Dave,

    When I was learning Tableau in early 2017, and asked my friend (both of us using BO since 2009) about LOD expressions, he explained to me using exactly the same sentences as you did here, in just 18 words.

    FIXED is the equivalent of IN.
    INCLUDE is the equivalent of ForEach.
    EXCLUDE is the match to ForAll.

    I think Tableau’s terminology is better though.

    Mahboob Mohammed

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.