Facebooktwittergoogle_plusredditpinterestlinkedinmail

Syntelli - Tableau 9.0 LOD Expressions

One less thing I have to do in SQL

“Can you tell me the average total monthly purchase amount our customers had month over month?” This seemingly simple question has driven many a Tableau user to madness or, even worse, SQL scripting. But now, with Tableau 9.0, Analysts have access to the powerful Level of Detail (LOD) Expressions.

LOD expressions could be compared to SQL Windowing (or OVER) functions. They allow for us to specify an additional layer of data manipulation (i.e. a sub-query) prior to our final data calculations.

In this article we will introduce the LOD expressions, discuss some Tableau history as to why these expressions are so important, then take a deep dive into the “Include” LOD expressions. We’ll walk through a use case were we can now get the average total monthly spend of our customers, in Tableau, without any data level manipulation to our transactional data.

I think this will be very informative to those ready to upgrade to Tableau 9.0, read on for more info or download the PDF file with the instructions below!

Download Tutorial


An Average of a Sum

Average of total customer spend by month is a question that inevitably gets asked in almost any transaction driven industry, and it seems to confound the very foundation Tableau was built upon – which is only bring in the data which is necessary into the visualization.

However, if I have a multiple transactions from multiple customers over multiple months, and I want to know the average of the total purchases by the customers each month, I would first need to sum the transactions by customer by month, then take the average of the amount spent each month. This means I have to use a dimension (i.e. customer) that I do not want to include in the visualization!

Let’s take a look at some sample data to see what exactly is going on here:
Tableau Beta 9 - LOD 1

Here we have some transactions from three customers A, B, and C during the months of February and January of 2014.
Now let’s do some basic visualizations in my trusty Tableau 8.3:

Tableau Beta 9 - LOD 2

I can even get the sum of the average spend of each customer per month (although I’m not sure why anyone would want to do that…):
Tableau Beta 9 - LOD 3

But there is no easy way to sum up what each customer spent that month, then take the average of the total customers’ monthly purchases.
Why is that? We have to go back and see why Tableau was created in the first place…

In the beginning

Tableau began as a tool to make visualizations on top of structured databases. And it was good. Classification and Ordinal data were assigned to “Dimensions” while numeric data were assigned to “Measures” with little loading time as these were all pulled from the schema. Loading only occurred when a user dragged these dimensions and measures to the “shelf” and Tableau would construct the database query for you and pass it to the underlying database.

Although Tableau uses its own custom query language called VQL (Visual Query Language) I’ll give some examples with SQL based on the above visualizations.

Total Spend by Month (SQL):
Here we only have four total values to return – two months and the sum of amount spent for those months. The query would be as follows:

Tableau Beta 9 - LOD 4

We can see the similarities between the Tableau “gems” we drag over to its shelf and the syntax in our SQL query. Our measures are aggregated (i.e. SUM([Spend])) and our dimensions are contained in the “GROUP BY” clause (i.e. GROUP BY MONTH([Date])).

Tableau has created a way for us to create visuals, directly from a database, without learning any SQL! Fantastic!

Average Total Spend by Customer by Month

But there is a problem… Now I want to answer the question from the beginning of the article “What is the average amount spent per month by customers?”

Ironically, this is somewhat easy to do in SQL

Tableau Beta 9 - LOD 5

While this may look complicated, all we are doing is selecting from the items returned by another query, i.e. a sub query.

Our sub query returns the following table:

Tableau Beta 9 - LOD 6

Then we repeat the same type of grouping operation in our previous query to get the average by month of the above table:

Tableau Beta 9 - LOD 7

But here lies the problem, the data must be pre-aggregated in order to calculate the average of sums. Prior to 9.0 there was no way to communicate to Tableau that the data element you are dragging onto the shelf should not be used in the visual, but used to pre-aggregate the data before your final calculations.

The only solution was to either use custom SQL when connecting to a data source or have a new SQL View created, where both solutions have the data pre-aggregated by month and customer. This solution is very sub-optimal. We have limited our ability to divide the data into weeks or days and we can no longer access the individual customers.

But this has changed in Tableau 9.0!

Enter Level of Detail Expressions

Level of Detail (LOD) expressions let us tell Tableau to aggregate the data at another level before aggregating for the final visualization. There are three specific expressions in the LOD toolbox:

Include

First we will review the “Include” expression. Here is where we will also answer the question posed at the beginning of the article “What is the average total amount spent by customers each month?”

Remember how complex the previous solutions were in Tableau? Here’s how you do it now
Tableau Beta 9 - LOD 8

That is all you need to do!

Now we take the average of this expression in the Tableau shelf and we have our answer:

Tableau Beta 9 - LOD 9

All we had to do was tell Tableau to include the sum of spend by customer id in the aggregation calculation. For a little more detail as to what’s happening behind the scenes let’s look at our original SQL script and see which parts correspond to our visualization:

Tableau Beta 9 - LOD 110

Green corresponds to our row expression, blue to column, and orange to the level of detail. You can see that everything in the subquery is either in the LOD expression or the column, i.e. nothing corresponding to the row expression is contained in the subquery. Our row expression is only used as a top level aggregation and the top level aggregation only contains values included on the shelf.

Notice something interesting, however, about our subquery. Not only does it include the aggregation specified by our LOD expression, but it also includes any other shelf aggregation (i.e. “month”) apart from our row aggregation (i.e. average of our LOD expression).

Okay, that’s good and all, but what if I don’t want to view the data month by month? What if I want a single number showing the average monthly spend of my customers? You would think just dragging the date value out of the equation would solve it, unfortunately, this is not the case:

Tableau Beta 9 - LOD 12

Somehow we have a number higher than our two previous monthly averages. That shouldn’t happen! Let’s look at our code:

Tableau Beta 9 - LOD 13

Ah, well there’s our problem, without including month, we are now taking the average each customer spent across all the data. We can fix this by including not only customer, but also month in our include expression:

Tableau Beta 9 - LOD 14

Notice that we did have to make a calculated field indicating the month of the transaction, LOD expressions will not allow other custom expressions to the left of the colon; however, they will allow calculated fields.

Our new equation would match in the following code:
Tableau Beta 9 - LOD 15

Notice how our date aggregation is now orange and only contained in the subquery. This is exactly what we want and returns the appropriate value:

Tableau Beta 9 - LOD 16

Fixed and Exclude

Include is probably going to be the LOD expression most used by Tableau Analysts; however there are Fixed and Exclude expressions as well. In an upcoming article we will explore these in depth, but the information contained here should be enough to get you started with LOD:
Fixed: Used when multiple hierarchies are present in a visualization (e.g. year > month > week) will fix the calculation to a specified level of the hierarchy. Useful for percent of total calculations (e.g. % of monthly revenue by week)
Exclude: The opposite of Include. Tells Tableau not to use a value on the shelf when performing a calculation. Useful for comparing overall totals to dynamic hierarchies.


Data Analytics Demo

 


Daniel-SmithDaniel Smith, MBA CPA CMA
Principal Consultant
About Daniel:
Using Business Intelligence platforms to bridge the gap between Advanced Data Analytics and the efficient effective principles of accounting, Daniel applies technology and mathematics to make business faster and smarter.
Daniel has managed solutions for diverse client sectors such as as advertising, military, insurance, and oil & gas. These solutions include Business Intelligence Platform management, online key performance indicator identification and tracking, to full predictive data model construction. Although the analytic solutions are often mathematically complex, Daniel’s presentation and academic background ensures any insights delivered by solutions are relevant and simple to understand.


Facebooktwittergoogle_plusredditpinterestlinkedinmail

2 Comments

Poornima · April 30, 2015 at 4:32 am

We are currently facing the challenge in Tableau Report. The data is fetched from Salesforce.

Please find the below problem statement . Please let me know if you need more information regarding the same.

Problem statement:
We have an requirement to display the data as month basis – where data has to be distributed equally from start date to end date.

For example start date is 1/Jan/2015 and End Date is 31/Jan/2016 then over the period for every month data has to be distributed across the months between 1/Jan/2015 and 31/Jan/2016.

Key Points:

In tableau we need to display the 12 months representation from the current month in x-axis. So we have taken Sheet named Months as the primary source, which will have static months list from current month to next twelve months. This would not be the correct way. But to achieve 12 months in x-axis we have taken this excel sheet.

As we are using Tableau Desktop 8.3 , would this kind of calculation we can do it tableau desktop 9.0.

Kindly provide some idea/solution how can we achieve the same in tableau.

    Daniel Smith · May 1, 2015 at 3:13 pm

    Poornima,

    Thanks for reaching out!
    Tableau 9.0 can probably help, but we would need to know more about the situation. Can you email us at info@syntelli.com and we can schedule a consultant to speak with you?

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

blog

How To Export Google Analytics Data into a Data Lake: Case Study

One of our clients recently presented a unique and advanced analytics challenge. Through an innovative approach, the Syntelli team successfully implemented a simple, yet effective solution, to satisfy several other business requirements. Ultimately, the team Read more…

blog

How To Unlock Analytics Maturity in Your Organization: 3 Skills For Success

This is our third and concluding post in the series of achieving analytics maturity for finance and accounting professionals. Read the first part here, and the second part here. As seen in our Data Science Read more…

blog

How To Become Analytically Mature in Accounting and Finance – Part Two

Refer back to part one for intro to Reporting and Business Intelligence for professionals in Accounting and Finance. How to Become Analytically Mature: Data Science Examples of Tools Considered: SAS, RapidMiner, Knime, SPSS, Rattle

 

Login

Register | Lost your password?