Tableau 9.0 has moved from alpha to beta and I’m loving it! There are many new features to enjoy:
- Improved calculation editor
- Ad-hoc calculations
- New functions (like regular expressions)
- Data interpreter and cleanup tools like pivot and split
- Drag and drop analytics
- Map Search
- New selection options (radial, lasso)
- Responsive tool-tips
- Formatting for Story Points
- And more!
Many of these are incredibly cool and make Tableau that much more smooth and easy to work with. I’m sure I’ll blog on many of these in the coming weeks.
But there is one new feature that is a true game changer…
(I don’t use the term lightly)
Level of Detail Calculations
Previously, in Tableau, every aggregation was done at the level of detail based on dimensions in the view. You could roll things up using totals and subtotals. You could even get a little fancy using sets or table calcs, but these all had their limits.
No longer!
Now you can use Level of Detail aggregations. Here are some basic examples:
Fixed: calculates the aggregation at the level of detail specified by the list of regardless of any dimensions in the view.
{FIXED [Department] : AVG([Sales])}
The above code calculates the average sales per Department, regardless what other dimensions are in the view.
Include: calculates the aggregation at the level of detail specified by the dimensions in the view and the dimensions included in the code.
{INCLUDE [Item] : AVG([Sales])}
The above code calculates the average sales at the level of detail defined in the view but includes the dimension Item, even if Item is not in the view.
Exclude: calculates the aggregation at the level of detail specified by the dimensions in the view, excluding any listed in the code.
{EXCLUDE [Category] : AVG([Sales])}
The above code calculates the average sales at the level of detail defined in the view but does not include Category the dimension as part of the level of detail, even if Category is in the view.
How about a real-life example?
Binning By Aggregate
You may remember that I had previously shared four possible ways of Slicing by Aggregate. This is when you ask questions of “How many had how many?”
- How many patients had how many readmissions? (e.g. I want to know that 500 patients were readmitted 1 time, 335 were readmitted 2 times, 23 were readmitted 3 times, etc…)
- How many students had how many classes?
- How many products had how many sales?
These types of questions were very difficult (almost impossible) to answer in Tableau without custom scripting or using some crazy options (non-Cartesian visualizations, table calculations, sets) because you have to aggregate the first answer and then use that to slice (or bin) another aggregate.
Let’s say I have a list of students and classes they are taking:
I want to know how many students took how many classes (e.g. 9 students took 6 classes, 5 students took 5 classes, etc…)
In steps Level of Detail (specifically fixed) and solves my problem. I simply create a calculation like this:
The field shows up as a Measure by default (but I can actually re-characterize fixed level of detail calcs as dimensions!) Now I just create a bin (right–click the new field and select Create > Bins)
And then I can build the view that answers my question:
This solution doesn’t have any of the drawbacks of the previous work-arounds. It has nice UI, plays nice with action filters, and isn’t incredibly complex.
And this just barely scratches the surface of what Level of Detail calculations can do! I’m sure we’ll be seeing a lot more of them in the future.
Want to learn about more Tableau 9 new features? Check out my other favorite 9.0 features and Data Prep and the Tableau Data Interpreter.
Hi Joshua,
Wow! Wow! Wow! – the ability to easily fix, include and exclude dimensions was something that I was missing for a long time in Tableau and I am so happy to hear that it is coming in v9. I come from PowerPivot where doing these kind of things were much easier and I am so glad that Tableau is moving in that direction. I was not planning to try the v9 beta but now you have given me a reason! Thanks a lot for bringing the glad tidings , Joshua!
Thanks Siraj! You’ll love the beta. The only downside is having to wait until the production release… but hopefully it won’t be long…
Joshua,
Thanks for that post. I’ve been waiting for some of these things for a long time. Tableau is starting to implement some powerful techniques. Nice example.
Ken
Thanks Ken! It certainly blows the door open on the kinds of analysis possible.
Joshua:
This is a very nice and detail post on the LOD and you are absolutely right that it is going to knock some socks off with calcs.
..kk
Thanks KK!
Hi Joshua,
Great Post. Thanks for posting the example. The above enhancements in Tableau 9 gives us some powerful techniques, and makes it all the more simple to use.
The simplicity along with the power is what amazes me most.
Simplicity IS power. Or rather simplicity reduces friction and enables more power to be applied elsewhere.
Take the new connect ribbon. Pushing commonly used servers to the top means I no longer have to scroll down to “T” to use Teradata. A simple change but very much enjoyed by me.
There are so many new features like that – one hardly notices them but they make life so much better.
I hope some powerful calculations like this makes Tableau outstanding in the BI market. Waiting for the beta release.
These are powerful and will open up many possibilities for answering previously difficult questions. Contact your Tableau Rep to get in on the beta program (though I imagine production won’t be too long from now).
Thank you Joshua!!
Great to hear from you Susan!
Wow – I will definitely be making good use of this one.
Just so happened to be having a go at partner challenge week 21 viz – and made great use of feature in v9 beta to answer the question with a much better solution than v8.2
I looked up the challenge, and you are right, it would be very much easier with LOD calcs.
It might be a game changer for Tableau but Qlik’s been doing that for years now –
It would be interesting to see a timeline for both platforms and what features were introduced when. Even more important than lists of features though, is the flow a platform gives for accomplishing various tasks and answering certain questions. Features may enhance (or even sometimes detract from) being able to accomplish goals seamlessly. In this case, LOD calcs extend Tableau’s paradigm for working with data (a paradigm I love!) Not having much experience with Qlik, I’m curious, what recent features would you say have been game-changers?
Thanks for the details Joshua. Level of detail based aggregations surely are game changers. I have been dreaming of having Business Objects like FORALL, FOREACH possibilities in Tableau. Looks like my dream is coming true. But not sure when is the production release though.
In my experience, as soon as a version of Tableau goes to beta, the production release is only a month or two out.
installed v9-today..experiencing tableau 9 beta now…just WOW
thanks joshua to help us to make better use of v9.
plz share some more v9 features with lucid examples as above.
now,these features reduces the limitations of tableau as a BI tool.
rivalry with qlik in right direction.
analysis as a separate tab makes in more usable/interactive.
way to go tableau!!!
Thank you for your kind words. I look forward to sharing more features and in fact, have started to produce a video series to do just that for my employer (Teknion Data Solutions – http://www.teknionusa.com). You can subscribe to the YouTube channel here: https://www.youtube.com/channel/UCnG4BTHBe3RCA5xgyevDwnw
interestingly enough, lack of this feature in Tableau 8 was the reason we decided to go with QlikView 11. Well, one of the reasons 🙂 Qlikview has super powerful (but hard to master) set analysis expressions which you will see in every single QlikView dashboard.
I am glad Tableau is catching up with QlikView in this regard and Qlik is catching up with Tableau with the new Qlik Sense product – this is truly the competition working its magic and making products better for end users and developers 🙂
Tableau has had table calculations, which could do much of the analysis but were “hard to master”. Table calculations will remain a powerful part of the tool, but the new level of detail calculations are actually fairly easy. Granted, one still has to have a good grasp of granularity, but Tableau has really made things much easier – LOD calcs keep everyone in the flow of analysis. It will be fun to watch the various BI and analytics platforms evolve over the next few years!
Agree that this is a welcome addition but it looks like I’m alone here in being somewhat disappointed that it has been implemented as a purely scripting technique without much in the way of visual cues or a “visual” implementation (please educate me if otherwise!). That being said, I appreciate that brevity is being prioritised here but it seems to contradict the spirit of why Venn diagrams appeared in “Edit the Data Source”.
John, I don’t think you are alone – but Tableau is also aware that there is much more to be done with LOD calcs and Table calcs – including making things more visual. Check out this thread on the forums where the same point is raised by Jonathan Drummey and Dan Huff, a senior Tableau engineer, responds that they’re not done yet!
What’s great is that LOD calculations aren’t limited to just one field!
For example:
{ FIXED [Equipment ID], [Invoice Number] : SUM([Line Item Amt]) }
allows me to sum per piece of equipment, per invoice; summing on invoice alone wouldn’t work (since there are multiple bits of equipment on one invoice), and neither would equipment ID (since each one has multiple invoices).
Yes, LOD calcs were implemented exceptionally well. I keep finding new uses all the time (and wishing I had them when working with clients who have older versions!)
Great feature that I am looking for. But, how does {Include [dimension]: sum(measure)} work? say if in my viz I have dimension A, B. Then Include C means level of detail turns to be A,B,C. So, adding this {include…} makes no help to calculation.
Hello,
I am wondering if it is possible to use INCLUDE to include only certain values within a dimension. I am trying to create a calculated field for Percent of total using certain values on the dimension for one % of total measure and others for a different % of Total.
There are reasons why I can’t use filters and parameters to accomplish this.
Thanks!
I don’t know that INCLUDE would be the way to do it — but it might take a little more detail for me to truly understand what you want to accomplish. My first though is to wonder if you can check the values at a row level and then count (or sum) the matches for the numerator. Something like: SUM(IF [Dim] = “Value I want” OR [Dim] = “Other value I want” THEN 1 ELSE 0 END) / SUM(1)