Screen Shot 2014-06-14 at 4.08.55 PM 1

At the Level – Unlocking the Mystery Part 1: Ordinal Calcs

There was a Tableau forums thread on At the Level awhile back where Matthew Lutton asked for an alternative explanation of this somewhat puzzling table calculation configuration option, and I’d promised I’d take a swing at it. Plus, I’ve been deep into book writing about shaping data for Tableau, and a taking a break to write about obscure table calc options sounds like fun! (Yes, I’m wired differently.)

Read on for a refresher on addressing and partitioning and my current understanding of uses of At the Level for ordinal table calculations such as INDEX() and SIZE(). Part 2 will cover LOOKUP(), and Part 3 will cover WINDOW_SUM(), RUNNING_SUM(), and R scripts. If you’re new to table calcs, read through at least the Beginning set of links in  Want to Learn Table Calculations. Thanks to Alex Kerin, Richard Leeke, Dimitri Blyumin, Joe Mako, and Ross Bunker for their Tableau forum posts that have informed what you’re about to read.

Review of Addressing and Partitioning

When we set the Compute Using for a table calc, we are setting the addressing—Compute Using and addressing are synonyms. The addresses—also called “rows in the partition”—are the distinct combinations of values of the dimension(s) used for addressing. Tableau computes a table calculation result for each address. Here’s a quick example using an INDEX() function in Superstore Sales:

2014-03-16 16_27_56-Tableau - explaining at the level

The addressing is the default Compute Using of Table (Down), which for this pill arrangement means the addressing is on Department and Category, so Tableau is computing the result for each combination of Department and Category.

All of the dimensions in the view that are not part of the addressing are automatically part of the partitioning. Tableau will restart the computation of the table calculation for each new distinct combination of values of the dimension(s) (and the occasional discrete measure) used for partitioning. What “restart” means depends on the particular table calculation function in the case of INDEX() that means starting back at 1. So if we set the Compute Using (addressing) of the Index calculation to the Category dimension, then it will partition (restart) on each Department:

2014-03-16 16_32_00-Tableau - explaining at the level

One of the nice things that Tableau does is that as dimensions are added to the view, they are automatically added to the partitioning. For example, in Superstore Sales if we have a % of Total Sales Quick Table calc on Sales that is addressing on the Order Date:

2014-03-16 16_32_30-Tableau - explaining at the level

And then we add the Region to the view, Tableau will continue to address on the Order Date and partition on the Region:

2014-03-16 16_33_03-Tableau - explaining at the level

Now, if I expand out the YEAR(Order Date) to include the QUARTER(Order Date), I see this view where the % of total is being computed along all Quarters for each Region:

2014-03-16 16_33_28-Tableau - explaining at the level

What if I want to generate a set of results for each Year & Region?

Oftentimes I find myself thinking about table calculations is that I first consider the partitions—the boundaries—of the table calculation. Since Tableau requires me to specify the table calculation in terms of the addressing, that means I’ve got to do a little bit of mental gymnastics. There are three dimensions in the view – Year, Quarter, and Region, so if I want to partition on the Year and Region, then I need to address on what’s left—the Quarter. However, there’s no easy option on the table calc pill to just set the addressing to the Quarter, the only date option is Order Date:

2014-03-16 16_34_03-Tableau - explaining at the level

This is an effect of using Tableau’s built-in date hierarchy. Fortunately, we can still use Tableau’s date hierarchy by using an alternative means to set the addressing. To do that, we have to use the Edit Table Calculation… option, which brings up this dialog:

2014-03-16 16_34_23-Tableau - explaining at the level

Then on the “Summarize the values from:” combo box I’ll choose Advanced…, which brings up the Advanced dialog:

2014-03-16 16_35_11-Tableau - explaining at the level

This shows all the dimensions in the view, with the dimensions used for partitioning on the left and addressing aka Compute Using on the right.  The order of the dimensions for addressing is important, because that determines how those dimensions are going to be sorted for the table calculation, and also affects At the Level.

Here we can see that Tableau now shows not just Order Date as a single value, but includes both the dimensions of Order Date in the view. Now I can double-click on Quarter of Order Date to make it part of addressing, then click OK a couple of times to close the dialogs, and see that now the % of total is computed along the Quarter for each Region & Year:

2014-03-16 16_37_36-Tableau - explaining at the level

Ok, so that’s the super-quick refresher on Addressing and Partitioning, now to begin our exploration of At the Level.

At the Level

Rather than define what At the Level does just yet, it’s easier to see it in action first. One thing that confused me about At the Level was that I’d only need it once in awhile, and then when I used it the behavior always seemed different. The factor that I’ve identified is that At the Level does behave somewhat differently for three categories of table calculations:

  • Ordinal calculations such as INDEX(), FIRST(), LAST(), SIZE(), LOOKUP(), and the RANK_ functions.
  • WINDOW_, RUNNING_, and the R SCRIPT_ functions all aggregate measures along the addresses of each partition.
  • TOTAL() is an aggregation of the inner measure at a different level of detail. At the Level has no effect on TOTAL().

This post covers some of the ordinal calcs, and will be continued in Part 2 for LOOKUP. Part 3 will cover the WINDOW_, RUNNING_, and R scripts.

At the Level for Ordinal Table Calculations

We’ll start the exploration of At the Level with the ordinal table calculations. Here’s that view of Index addressing on Category:

2014-03-16 16_45_19-Tableau - explaining at the level

In the Edit Table Calculation dialog, At the level is greyed out:

2014-03-16 16_47_08-Tableau - explaining at the level

This is because there’s only one dimension in the view. At the Level is only available when there are two or more addressing dimensions.

If we put both Department and Category into the addressing section of the Advanced Compute Using dialog, we’ll see the same results as Table (Down), and At the Level is now available:

2014-03-16 16_49_20-Tableau - explaining at the level

 

Explaining the At the Level Combo Box

If we click on the At the Level combo box, we see three options: “Deepest” and the two selected addressing dimensions:

2014-03-16 16_50_16-Table Calculation [Index]

What appears in this menu are only the addressing dimensions and the “Deepest” option. “Deepest” sets At the Level to be the bottom-most dimension in the menu, and has no effect on the results of the computation, so if I don’t need At the Level then I leave it at Deepest.

I’ll regularly see workbooks where someone is asking a table calc question and has At the Level set to the bottom-most dimension. It’s a good sign that the questioner is hunting and pecking to get a table calculation result, and/or was taught by someone (or a series of someones) who hunted and pecked until they found something that returned desired results, without really understanding what Tableau is doing. Please know that I don’t mean this as criticism at all, the fact is that the official Tableau Desktop documentation (as of 8.1) only mentions At the Level once in passing and never describes At the Level’s behavior, so it’s been up to us as users to find ways to get the job done and then pass them on to other users. We’re each working with the best information we have and the tools at hand, like Grandma did. And I wouldn’t be where I am without the support of other users as well as a many Tableau staff who have answered my support queries, my goal for posts like this is to expand the available knowledge.

If we specify a dimension for At the Level and then sometime later add other dimensions to the addressing below the chosen At the Level dimension, Tableau will leave At the Level on the chosen dimension. I’ve never used this option myself, though that’s more due to how I put together views: I try to get all the dimensions in the view (on Rows, Columns, Pages, and the Marks Card) first, and add all the dimensions for addressing, and only then set At the Level.

Seeing Results

To review, if we choose Deepest or the bottom-most dimension there won’t be any effect. If we choose a higher-up dimension, like Department in this case, so the addressing for the Index calculation is on Department, Category, At the Level Department, then the table calculation returns different results:

2014-03-16 16_53_29-Tableau - explaining at the level

Since the addressing is on all the dimensions in the view there is only one partition for the entire view. What Tableau is doing is only incrementing the Index value for each new Department while still returning a value for every address/row in the partition. I’ll step through this table for the first several rows:

  1. Furniture/Bookcases is the first address and gets an Index of 1.
  2. Furniture/Chairs & Chairmats is the next address and has the same value of Department, so the Index is not incremented and remains 1.
  3. Furniture/Office Furnishings still has the same value of Department, so the Index is still 1.
  4. Same for Furniture/Tables.
  5. Office/Supplies Applicances has a new value of Department, so the Index is incremented to 2.
  6. Office Supplies/Binders and Binder Accessories has the same value of Department, so the Index is not incremented and remains 2.
  7. And so on…

This is true for all of the ordinal calculations, here’s a view with several of them:

2014-03-16 17_00_49-Tableau - explaining at the level

The SIZE() calc is only incrementing on the Department, and there are 3 Departments, so every address gets a value of 3. The FIRST() calc again is only incrementing on the Department,  so all the values of FIRST() for Furniture have the same 0 result, then all the values for Office Supplies have the same -1 result, and so on.

Here’s my current attempt at a working definition of At the Level for ordinal calcs:

Each distinct combination of values of the addressing dimension used for At the Level and all addressing dimension(s) above the At the Level dimension increment ordinal table calculations. Addressing dimension(s) below the dimension used for At the Level do not increment ordinal table calculations, and instead return the result of the prior address aka row in the partition for each of those addresses.

The default setting of “Deepest” or choosing the bottom-most addressing dimension tells Tableau to increment an ordinal table calc on each address, when we set the At the Level dimension to be a higher addressing dimension then we’re separating the incrementing of the ordinal calcs from the addressing.

A key point here is that this only applies to the addressing dimensions. If I drag another dimension into the view, like YEAR(Order Date), that is automatically added to the partitioning so the calculation totally restarts for each new Year:

2014-03-16 17_08_54-Tableau - explaining at the level

However, if we add that third dimension to the top of the addressing and leave the At the Level on Department, we get very different results:

2014-03-16 17_10_12-Tableau - explaining at the level

Based on these settings, Tableau now increments the Index for each and every new combination of Year and Department, while repeating the values for each Category within those dimensions.

You may ask, when might we want to use At the Level for an ordinal calculation? To explain this, I need to define granularity: The granularity of the view is the distinct combination of values of dimension(s) on Pages, Rows, Columns, and a given Marks Card. The most common use case for At the Level that I’ve seen is when we want the table calculation to increment at a coarser granularity than the granularity of the view. In the example above with Department and Category in the view, we were incrementing the Index on the coarser granularity of Department.

At the Level and Sparse Data #1

At the Level really shows off it’s power when we’re dealing with sparse data. Thanks to  Dimitri Blyumin, here’s a great example. Before Dimitri found this solution, we had to deal with situations like this using a custom calc built with LOOKUP() and PREVIOUS_VALUE(). Here’s a view with Container on Rows, SUM(Sales) on Columns, and Department on Color:

2014-03-16 17_15_25-Tableau - explaining at the level

What if I want a unique rank or index for each Container? In other words, I want to see this:

Screen Shot 2014-06-10 at 6.29.43 PM

I can bring in an Index, make it discrete, and see this:

2014-03-16 17_17_12-Tableau - explaining at the level Starting with Large Box, the Index is returning multiple values. Without At the Level, No matter the compute using settings I pick of Table, Department, Furniture, nothing will work. Here’s a view that shows why:

2014-03-16 17_19_32-Tableau - explaining at the level

There is no data for Jumbo Box/Office Supplies, so the Index for Office Supplies is skipping that combination and continuing on, so for Large Box and on down there are two values of the Index for each.

If you just have a crosstab with Department on Columns and Container on Rows, and have the Index calc with a Compute Using of set to Department, you’ll see a value for Jumbo/Box Office Supplies padded in due to domain completion. This is one of the cases where building table calcs as a crosstab shows results that we don’t expect. To avoid unwanted domain completion, the trick is to put ATTR(Domain) on Columns, and the Domain dimension on the Level of Detail Shelf.

The solution to getting the results we want is to use an Advanced Compute Using on Container, Department, At the Level Container:

2014-03-16 17_22_50-Tableau - explaining at the level

This now only increments the Index for new values of Container, and when we put that Compute Using into the view, we get the desired results:

2014-03-16 17_24_13-Tableau - explaining at the level

In a sense, At the Level lets us “ignore” the Department dimension and only increment the Index on Container. However, Tableau is still computing a result for each Department in each Container. I’ve come to think that a better description is that At the Level gives us control over what addressing dimension(s) we want the ordinal calc to incremement on.

At the Level and Sparse Data #2

Here’s another example drawn from the Tableau forums. In this case the view is showing 10 customers, but 12 marks because there are multiple Customer Segments for some customers. The goal is to be able to say in the Title that there are 10 customers displayed (or however many customers are selected).

Screen Shot 2014-06-14 at 9.55.13 AM

Since SIZE() counts the number of addresses in the partition, and Customer Name is a dimension, it would seem to be the perfect calc to count the number of customers.

We can also use WINDOW_MAX(INDEX()) to duplicate what SIZE() does, but SIZE() does it in one function.

However, when we put SIZE() into a view, without using At the Level there’s no combination of addressing dimensions that will get us to 10. We’ll get 12, 4, 3, etc.

Screen Shot 2014-06-14 at 10.05.04 AM

With a Compute Using of Customer Name, Customer Segment, At the Level Customer Name we can set up SIZE() to only increment the # of addresses for a new Customer Name, resulting in a total of 10:

Screen Shot 2014-06-14 at 10.07.12 AM

An Alternative Using Rank

This is a bit of a tangent, I’ve included it here because it serves as an example of how there are often multiple routes to a solution in Tableau and it can be one way to deal with a complication of At the Level. Tableau’s Rank table calculation functions take an aggregate measure as an argument and rank the values of that measure. Often we think of using things like RANK(SUM([Sales])), but the measure doesn’t have to be numeric, it can be any data type. So RANK_DENSE(MIN([Customer Name])) with a Compute Using of Customer Name, Customer Segment will cause Tableau to rank each customer name alphabetically, with ties counting as the same number. This gives us a distinct value for each Customer Name:

Screen Shot 2014-06-14 at 10.14.46 AM

The largest rank is the number of Customer Names, so we can take the calc and create a fully optimized calc with PREVIOUS_VALUE(WINDOW_MAX(RANK_DENSE(MIN([Customer Name])))). The WINDOW_MAX gets the largest rank, and the PREVIOUS_VALUE() is an optimization so the calc is only performed once for each partition.

Screen Shot 2014-06-14 at 10.16.16 AM

There’s a quirk with At the Level where if the dimension used for At the Level (or any addressing dimensions above it) are on the Level of Detail Shelf while other addressing dimensions are on Rows, Columns, and/or Pages, Tableau triggers domain completion for those addressing dimensions. This is avoided by the Rank calc since it doesn’t need At the Level at all.

Another to prevent unwanted domain completion is to put all your addressing dimensions on the Level of Detail Shelf, and use discrete measures such as ATTR(myAddressingDimension) to generate the proper headers.

Conclusion

The clue for me when I might want to use At the Level for an ordinal calc is when some aspect of the view requires a finer granularity, while I want my ordinal calc to “ignore” or skip over that finer granularity to increment at a coarser granularity. For me, At the Level is kind of like the pipe wrench that sits in the bottom of my toolbox: not needed and unnecessary until it is exactly the right tool for the job.

Part 2 of this series will cover some interesting aspects of using LOOKUP, which adds another wrinkle to how At the Level works. Part 3 will go over WINDOW_, RUNNING_, and R scripts.

17 thoughts on “At the Level – Unlocking the Mystery Part 1: Ordinal Calcs

  1. Ashish

    Thank you! This is so informative. I was looking something similar in Tableau as against the Level metric in MicroStrategy.

    Reply
  2. Dave Walp

    Thank you very much for this (and your many other contributions)! I continue to wrestle with Tab calcs.

    Reply
  3. Pingback: Tableau’s TC14 Conference – #DATA14 | mluttonbi

  4. Matthew Lutton

    I am finally getting around to reviewing this in its entirety a second time, and I think it has begun to click in the context of Ordinal Calcs. Thank you, Jonathan, for taking the time to explore this in more depth!

    Reply
  5. Pingback: Tableau Request Live – The 2nd Episode: Data Densification | mluttonbi

  6. Pingback: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

  7. Keith Helfrich

    Now I’m also reading this for a second time. Two important little details have jumped out at me this time:

    – PREVIOUS_VALUE() is an optimization, so the calc is only performed once for each partition.

    – unwanted domain completion is likely what’s been completely confusing me when my SIZE() calcs aren’t working as expected, At The Level & all.

    Reply
  8. Siraj Samsudeen

    Hi Jonathan, a very insightful article and thanks a lot for your efforts to writing it. I found the examples really useful to think through how to really apply this. I came to this article after watching Joe Mako’s video on data densification that was on Matthew Lutton’s blog. Reading this has made the concepts that Joe explained even more clear in my mind.

    A few questions/comments:
    1. Any idea of when you are planning to write the part 2 and 3 as I would like to understand the effect of At the level on other calculations?

    2. You said “The order of the dimensions for addressing is important, because that determines how those dimensions are going to be sorted for the table calculation”
    Would you mind giving an example of where this matter even when the sorting is set to automatic. I was wondering about the order of the addressing for a while when the sorting is not really to set any specific measure and your post triggered my curiosity even more.

    3. You said “how I put together views: I try to get all the dimensions in the view (on Rows, Columns, Pages, and the Marks Card) first, and add all the dimensions for addressing, and only then set At the Level.”
    Thanks for sharing this insight on how you really put together views using advanced table calculations so that we don’t land in trouble. You remind me of the advice that I have gotten from the first computer science teacher way back in my engineering school days which I have found to be so helpful to me throughout my days as a programmer and now as well in the world of BI “The faster you go to the compiler, the later you finish the problem”. He was recommending us to really think things through on paper and get everything resolved on paper before turning to trying things out to get a solution. You seem to be following a similar methodology which I intend to copy from you and make it my habit so that I build good habits when I work with advanced table calcs in Tableau

    4. You said “The granularity of the view is the distinct combination of values of dimension(s) on Pages, Rows, Columns, and a given Marks Card.”
    Why would you consider Pages to determine the granularity? I am asking this to understand the Pages shelf better. The way I have understood Pages shelf so far is that it is like a Filter shelf as it helps us to take one value at a time and create a page (like applying one value in a Filter at a time). My thought was that Filters will not have any effect on the table calc (as it really goes on the Where clause in SQL). So should be the case with Pages, I thought – can you please let me know where I went wrong in connecting the dots?

    Reply
    1. Jonathan Drummey Post author

      Hi Siraj,

      Thanks for your questions! Here are some answers:

      1) Somewhere between a few and several months, I’m focused on book writing at the moment.

      2) Using Superstore, create a view with Department & Category on Rows (in that order), and an INDEX() table calculation on the Text Shelf with an Advanced addressing on Department then Category, using Automatic sort. You’ll see the Index go from 1-17 starting with the categories in Furniture, then Office Supplies, and so on. Now go back to the Index calc and change the addressing to Category, Department. The Index will change results and be based on the alphanumeric sort of Category. (If you swap the Department and Category pills on Rows so Category is the left-most pill that will be visible).

      3) I’m glad this makes sense to you! I learned that particular technique from Joe, of the many things I’ve learned from him it’s one of the most valuable.

      4) Pages *looks* like a filter in terms of the visual effect on the view, however under the hood Tableau isn’t issuing any additional queries to the data source, it’s only adding whatever pill(s) are on the Pages shelf into the query/ies it would normally make. So if you have a dimension on the Pages Shelf, that’s added to the GROUP BY of the SQL, if you have a regular aggregate measure then it’s computed as part of an aggregate query, etc. Another point is that, dimensions on the Pages Shelf can be triggers for densification just like Rows or Columns. The visual effect of the Pages Shelf is processed quite late in Tableau’s pipeline, after filters are applied and other calculations are completed (including table calculation filters), so it’s really acting more like a Hide than a filter.

      One way to get a sense of this in action using Superstore is to put Customer Name on Rows and Sales on Columns, then also put Customer Name on the Pages Shelf. You’ll see that Tableau is still rendering all the headers of the ~3000 customers, even though it changes which particular Sales bar it’s showing. There’s a single query issued at the very beginning that is effectively SELECT [Customer Name], SUM([Sales]) FROM Superstore, everything else is all happening in memory, and that’s what enables Tableau to also do the animation on the Pages Shelf.

      Jonathan

      Reply
  9. Pingback: Tableau Tips,Tricks,Best Practices - Calculation - Jenny (Xiao) Zhang

  10. Pingback: At the Level – Unlocking the Mystery Part 2: Rank Functions | Drawing with Numbers

Please add your thoughts and perspectives