Tableau LOD Expressions from Oracle Developer POV

The main purpose of any Business Intelligence system is to provide business users ability to answer questions that are important to running the part of the business for which they are responsible. For example:

  • Show me sales for each client by month.
  • Show me the average sales amount for each client by month.
  • Show me the running total amount by date.

If Business Intelligence system designed really well your business users just have fun asking questions of their data.

But there are many questions to answer Business Intelligence system need to work with data that has been aggregated to different levels of detail.

To address this type of queries Tableau 9.0 introduced a new feature called “Level of Detail (LOD) Expressions”. These expressions allow us to easily compute aggregations that are not on the level of details of the view.

Let’s have a look on these expressions from Oracle Developer Point of View…

The “Level of Detail” expressions has the following structure:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

Read more at https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.html

Include: Calculating At A Lower Level Of Detail

In this example, we’ll work with the following source table

Suppose you are analyzing the sales performance of each client and would like to know which client has the average order size by client and months.

If you choose AVG aggregation for “Amount” measure you will get incorrect results.

The result that you see, it’s average order line size instead of average order size.

To figure out the correct answer, you need to calculate the size of each order (sum the sales corresponding to each Order ID), and then average those values by clients.

To do this you need to create a new calculated measure “Sum Amount by Order” with the following formula.

{ INCLUDE [Order Id] : SUM([Amount]) }

And now you can see correct answer

From Oracle SQL Point of View, it’s the equivalent of the following SQL expression:

 Exclude: Calculating At A Higher Level Of Detail

To explain EXCLUDE expression let’s assume that you would like to see grant total sales amount by all clients and sales amount by clients and months. To get such result you need to create another new calculated measure “All Clients Amount” with the following formula:

{ EXCLUDE [Client] : SUM([Amount]) }

And you will the required result

From Oracle SQL Point of View, it’s the equivalent of the following SQL expression:

 Fixed: Specifying The Exact Level Of Detail

To explain FIXED expression let’s assume that you would like to see average order size and sales amount by client and months. To get such result you need to create another new calculated measure “Avg Order Size” with the following formula:

{ FIXED [Client] : AVG( [Sum Amount by Order] ) }

And you will the required result:

From Oracle SQL Point of View, it’s the equivalent of the following SQL expression:

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s