Replicating LOD calculations in SQL(ANSI Standard)

Version 1

    Introduction

    LOD (LEVEL OF DETAIL) calculations are generally used when you need to aggregate data at a different granularity level than the level of detail of your visualization. One way to easily understand this is by comparing it will SQL. In this article i will be explaining Tableau FIXED,INCLUDE and EXCLUDE LODs by contrasting it with SQL.

    I will be using global super store data set to explain the concept.

     

    FIXED LOD:

     

    Let say we want to see sales at the level of CATEGORY,SUB-CATEGORY along with sales at each CATEGORY level in our visualization.

    Tableau LOD expression(FIXED_LOD_SALES) :

    {fixed [Category]:SUM([Sales])}

     

    Below is the SQL do derive the same result:

    Here Inner query is equivalent to LOD expression and then joining back this result at the level of CATEGORY and SUB-CATEGORY.

    INCLUDE LOD:

    Let say we want to see average sales at the level of CATEGORY across table along with average sales at each CATEGORY across subcategory in our visualization.

    Tableau LOD expression(INCLUDE_LOD_SALES) :

    {INCLUDE [Sub-Category]:SUM([Sales])}

    Below is the SQL do derive the same result:

    Here Inner most query is equivalent to LOD expression and then 1st inner query is to get the category average across subcategory and then joining back this result at the level of CATEGORY.

     

    EXCLUDE LOD:

    Let say we want to see average sales at the level of CATEGORY,SUBCATEGORY along with the average sales at each CATEGORY across table in our visualization.

    Tableau LOD expression(EXCLUDE_LOD_SALES) :

    {EXCLUDE  [Sub-Category]:avg([Sales])}

     

     

    Below is the SQL do derive the same result:

    Here Inner query is equivalent to LOD expression where we find the average at category level only joining back this result at the level of CATEGORY,SUBCATEGORY.

     

    I hope this document will help you in understanding LODs specially if you are from SQL background.

     

    Please feel free to provide your feedback and inputs.