Analytic functions (LOD functions)

Limitations

Detail tables do not support LOD expressions.

Background

In Quick BI, you typically aggregate data using a fixed set of dimensions. For example, to view the total order amount by region and province, you place the region and province dimensions in the rows of a crosstab, add the order amount measure to the columns, and set the aggregation method to sum.

Quick BI generates the following SQL query:

SELECT
  ADR_T_1_.`area` AS T_AAC_2_,
  ADR_T_1_.`province` AS T_A9E_3_,
  SUM(ADR_T_1_.`order_amt`) AS T_AAD_4_
FROM
  `qbi4test`.`company_sales_record` AS ADR_T_1_
GROUP BY
  ADR_T_1_.`area`,
  ADR_T_1_.`province`
ORDER BY
  T_AAC_2_ ASC,
  T_A9E_3_ ASC

However, what if you want to view data at the region and province granularity and also display region-level totals in the same table? Or, what if you want to compare a province's data to its corresponding regional total, for example, by calculating a ratio or a difference? What if you want to find the product type with the highest sales amount in each province? To address these scenarios, you use LOD functions.

Use cases

Level of detail expressions specify the level of detail for calculations. The level of detail refers to the granularity of data aggregation, enabling you to handle visualizations with multiple levels of detail.

Use a level of detail expression when you need to add a dimension at a level of detail higher or lower than that of the current view. This lets you perform the calculation without altering the existing visualization.

Syntax

Level of detail (LOD) expressions are a powerful feature for creating complex calculations and aggregations. LOD expressions let you control the granularity of your calculations, letting you analyze data at a specified level (FIXED), a finer level (INCLUDE), or a coarser level (EXCLUDE).

LOD_FIXED

LOD_INCLUDE

LOD_EXCLUDE

Procedure

1. On the dataset edit page, click Create Calculated Field to open the configuration dialog box.

   

2. Enter a field name (①). In the field expression editor, select the LOD function and fields (②).

   

3. After you create the calculated field, click OK. A dashboard chart built with the new field shows the total order amount for each area; this value remains constant regardless of the product type.

   

Expression syntax

Basic syntax

The three LOD expressions use the following structure and syntax:

• Structure

  • LOD_FIXED{<dimension declaration> : <aggregate expression>}

  • LOD_INCLUDE{<dimension declaration> : <aggregate expression>}

  • LOD_EXCLUDE{<dimension declaration> : <aggregate expression>}

Example: LOD_FIXED{ [Order Date]:SUM([Order Amount])}

• Syntax description

  • FIXED | INCLUDE | EXCLUDE: keywords that specify the scope of the LOD expression.

  • <dimension declaration>: Specifies one or more dimensions that define the aggregation's level of detail. Use commas to separate multiple dimensions.

  • <aggregate expression>: The aggregate calculation.

Filter conditions

In Quick BI, you can also add a filter condition to the basic syntax. Use colons to separate the dimension declaration, aggregate expression, and filter condition.

• LOD_FIXED{dimension1, dimension2...:aggregate expression:filter condition}

• LOD_INCLUDE{dimension1, dimension2...:aggregate expression:filter condition}

• LOD_EXCLUDE{dimension1, dimension2...:aggregate expression:filter condition}

Implementation principles

In practice, the aggregated data from an LOD expression is often combined with raw data or data from other aggregation levels to provide a richer data view. In SQL, this typically requires using subqueries and JOIN operations. This section explains the implementation principles of FIXED, INCLUDE, and EXCLUDE level LOD expressions.

FIXED level

Returning to the scenario from the background section, what if you want to display data at both the area and province granularity, while also showing data aggregated only at the area level in the same table?

You can achieve this by creating a FIXED expression:

LOD_FIXED{[area]:sum([order_amt])}

In SQL, this is achieved by creating a subquery to calculate the order amount for each area and joining its result with the main query:

-- Simplified structure
-- lod_fixed{[area]:sum([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,			-- area
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,			-- province
    LOD_TM.`LOD_8BE7507A47AD81` AS LOD_2,			-- order_amt
    LOD_TP_0.`LOD_measure_result` AS LOD_3		-- lod_fixed{[area]:sum([order_amt])}
FROM
    (
        SELECT		-- Main query: area, province, sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            SUM(ADR_T_1_.`order_amt`) AS LOD_8BE7507A47AD81
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`,
            ADR_T_1_.`province`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT			-- LOD subquery: area, sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS LOD_measure_result
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`


-- Standard structure
-- lod_fixed{[area]:sum([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,
    LOD_TM.`LOD_8BE7507A47AD81` AS LOD_2,
    LOD_TP_0.`LOD_9D09E63F2E93FA` AS LOD_3
FROM
    (
        SELECT
            SUM(ADR_T_1_.`order_amt`) AS LOD_8BE7507A47AD81,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`province`,
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            LOD_TL.`LOD_55512959145CF3` AS LOD_55512959145CF3,
            SUM(LOD_TR.`LOD_measure_result`) AS LOD_9D09E63F2E93FA
        FROM
            (
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    sum(ADR_T_1_.`order_amt`) AS LOD_measure_result,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`,
            LOD_TL.`LOD_55512959145CF3`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`

INCLUDE level

What if you want to display data at the area granularity, while also showing the share of the province with the highest order amount within that area?

You can do this by creating an INCLUDE expression:

MAX(LOD_INCLUDE{[province]:SUM([order_amt])}) / SUM([order_amt])

In SQL, this is achieved by creating a subquery to calculate the order amount for each area-province combination and joining its result with the main query:

-- Simplified structure
-- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,		-- area
    LOD_TP_0.`LOD_EC796C51A8ABAB` / LOD_TM.`temp_calculation_0` AS LOD_1  -- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
FROM
    (
        SELECT	-- Main query: area, sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS temp_calculation_0
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (		
        SELECT		-- LOD subquery: area, max(order_amt)
            LOD_TP_0.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            MAX(LOD_TP_0.`LOD_measure_result`) AS LOD_EC796C51A8ABAB
        FROM
            (
                SELECT		-- area, province, sum(order_amt)
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    SUM(ADR_T_1_.`order_amt`) AS LOD_measure_result
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
                    ADR_T_1_.`province`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`

      
-- Standard structure
-- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TP_0.`LOD_EC796C51A8ABAB` / LOD_TM.`temp_calculation_0` AS LOD_1
FROM
    (
        SELECT
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS temp_calculation_0
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            MAX(LOD_TR.`LOD_measure_result`) AS LOD_EC796C51A8ABAB
        FROM
            (
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    SUM(ADR_T_1_.`order_amt`) AS LOD_measure_result,
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`

EXCLUDE level

Consider again the scenario where you want to display data at both the area and province granularity while also showing data aggregated only at the area level. The FIXED level LOD expression allows you to specify a dimension, such as area, to compute the aggregation and get the analytical data you need. Alternatively, since the chart already includes both the area and province dimensions, you can achieve the same result by excluding the province dimension from the aggregation. This is the principle behind the EXCLUDE level LOD expression.

You can do this by creating an EXCLUDE expression:

LOD_EXCLUDE{[province]:SUM([order_amt])}

In SQL, this is achieved by creating a subquery to calculate the order amount for each area and joining its result with the main query:

-- Simplified structure
-- lod_EXCLUDE{[province]:SUM([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,       -- area
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,       -- province
    LOD_TM.`LOD_140423A9870F07` AS LOD_2,       -- order_amt
    LOD_TP_0.`LOD_measure_result` AS LOD_3      -- lod_EXCLUDE{[province]:SUM([order_amt])}
FROM
    (
        SELECT      -- Main query: area, province, sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            SUM(ADR_T_1_.`order_amt`) AS LOD_140423A9870F07
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`,
            ADR_T_1_.`province`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT      -- LOD subquery: area, sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            SUM(ADR_T_1_.`order_amt`) AS LOD_measure_result
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`



-- Standard structure
-- lod_EXCLUDE{[province]:SUM([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,
    LOD_TM.`LOD_140423A9870F07` AS LOD_2,
    LOD_TP_0.`LOD_90EDFE3F5B628A` AS LOD_3
FROM
    (
        SELECT
            SUM(ADR_T_1_.`order_number`) AS LOD_140423A9870F07,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
        FROM
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`province`,
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            LOD_TL.`LOD_55512959145CF3` AS LOD_55512959145CF3,
            SUM(LOD_TR.`LOD_measure_result`) AS LOD_90EDFE3F5B628A
        FROM
            (
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    SUM(ADR_T_1_.`order_number`) AS LOD_measure_result,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                FROM
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`,
            LOD_TL.`LOD_55512959145CF3`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`

FIXED function use cases

A FIXED level of detail expression computes a value using only the specified dimensions.

Use case 1: Calculate total sales by region

Scenario

When your data has both Region and Province dimensions, you may want to calculate the total order amount for the region alone. In this case, use a FIXED level of detail expression, as it computes a value based only on the specified dimensions, ignoring any others.

Procedure

1. Create a calculated field.

   Field expression: LOD_FIXED{[Region]:BI_SUM([Order Amount])}

   Description: Calculates the sum of the order amount for each region.

   

2. Create a chart.

   In this example, we create a crosstab.

   Drag the Total Amount by Region field to the columns area. Drag the Region and Province fields to the rows area. Click Update, and the chart updates automatically.

   

   You can see that the total amount is the same for each region, regardless of the province.

Use case 2: Customer purchase frequency

Scenario

A sales manager wants to analyze customer purchase frequency to assess customer loyalty and retention.

Use the LOD_FIXED function to calculate the number of orders per customer. This result can then be used as a dimension to analyze customer purchase frequency.

Procedure

1. Create a calculated field.

   Field expression: LOD_FIXED{[customer_name]:COUNT([order_id])}

   Description: Calculates the number of purchases for each customer.

2. Create a chart.

   In this example, we create a bar chart.

   Drag the Number of Purchases field to the category axis / dimension area. Drag the customer_name field to the value axis / measure area and set its aggregation to count distinct. Click Update, and the chart updates automatically.

   

You can see that the largest group of customers has made 7 purchases, while the customer with the most purchases has made 58.

Use case 3: Profit percentage ranking by region

Scenario

A regional sales director wants to see the profit contribution of each region. While this is possible using Advanced Calculation -> Percentage of Total, a level of detail expression (LOD function) offers more flexibility.

In this example, use the LOD_FIXED function to create a ranking list of profit percentage by region.

Procedure

1. Create a calculated field.

   1. Field expression: LOD_FIXED{:SUM([profit_amt])}

      Description: This expression does not specify a dimension, so it aggregates the profit amount over the entire dataset to calculate the total profit.

   2. Next, to get the profit percentage for each region, divide SUM([profit_amt]) by the result of the previous LOD function. The full expression is: SUM([profit_amt]) / SUM(LOD_FIXED{:SUM([profit_amt])}).

      

2. Create a chart.

   In this example, we create a ranking list.

   Drag the Profit Percentage field to the measure area, and drag the area field to the dimension area. Click Update, and the chart updates automatically.

   

You can see that South China and North China are the top two contributors, while East China? and Southwest China have the lowest contribution and even negative profit. The "East China?" data point appears to be dirty data.

Use case 4: Annual new user statistics

Scenario

How can you determine if your product is growing? Besides tracking daily metrics like page views (PV) and unique visitors (UV), you can also measure user loyalty. For example, if long-time users continue to place orders, it indicates high product engagement. To achieve this, use a level of detail expression (LOD function).

In this example, use the LOD_FIXED function to perform an annual new user analysis.

Procedure

1. Create a calculated field.

   Field expression: LOD_FIXED{[customer_name]:MIN(DATE_FORMAT([buy_date], '%Y'))}

   Description: Calculates the earliest order year for each customer. You can adjust the granularity as needed by using date functions compatible with your database.

2. Create a chart.

   In this example, we create a line chart.

   Drag the Customer's First Purchase Year field to the category axis / dimension area, and drag the Sales Amount field to the value axis / measure area. Click Update, and the chart updates automatically.

   

You can see that users who started in 2013 still contribute significantly to total sales, indicating high user engagement.

Use case 5: Annual new customer trends

Scenario

Calculate the earliest purchase year for each customer, then analyze the distribution of the number of customers based on that year.

Procedure

1. Create calculated fields.

   1. Field 1: LOD_FIXED{[Customer ID]: min(BI_YEAR([Order Date]))}

      Name this field Customer First Purchase Year, set its data type to dimension, and its field type to text.

   2. Field 2: Number of Customers = count(distinct [Customer ID])

2. Create a chart.

   In this example, we create a bar chart to display the data. Drag the Customer First Purchase Year field to the category axis / dimension area and the Number of Customers field to the value axis / measure area. Click Update, and the chart updates automatically.

   

You can see the number of new customers acquired each year. The number peaked in 2021 and has declined significantly since then.

Use case 6: Customer distribution by purchase frequency

Scenario

Calculate the purchase frequency for each customer, then analyze the distribution of customers based on that frequency.

Procedure

1. Create calculated fields.

   1. Field 1: LOD_FIXED{[Customer ID]: count(distinct [Order ID])}

      Name this field Customer Purchase Frequency, set its data type to dimension, and its field type to text.

   2. Field 2: Number of Customers = count(distinct [Customer ID])

2. Create a chart.

   In this example, we create a bar chart to display the data. Drag the Customer Purchase Frequency field to the category axis / dimension area and the Number of Customers field to the value axis / measure area. Click Update, and the chart updates automatically.

   

You can see that the largest group of customers has a purchase frequency of 3.

Use case 7: Compare annual order amounts to 2023

Scenario

Compare order data across years against a benchmark year, such as 2023.

Procedure

1. Create a calculated field.

   sum([order amount])/
   LOD_FIXED{:
   sum(
   case when BI_YEAR([order date]) ='2023' 
   then [order amount] else 
   0 end)} 
   -1

   Expression breakdown:

   1. The denominator LOD_FIXED{:sum(case when BI_YEAR([order date]) = '2023' then [order amount] else 0 end)} calculates the total order amount for 2023.

   2. The full expression calculates the percentage difference between each year's order amount and the 2023 order amount. Name this field Comparison with 2023.

2. Create a chart.

   In this example, create a crosstab. Drag the Order Date (year) field to the rows area. Drag the Order Amount and Comparison with 2023 fields to the columns area. Click Update, and the chart updates automatically.

   

You can see the order amount for each year and its comparison with 2023.

Use case 8: Profit and loss days analysis

Scenario

Based on daily profit, tag each day as either "Profit" or "Loss." Then, count the number of days for each tag, grouped by the order's year and month.

Procedure

1. Create calculated fields.

   1. Field 1:

      case when 
      LOD_FIXED{[Order Date]:sum([Profit])} > 0 
      then 'Profit' 
      else 'Loss' 
      end

      Name this field Daily Profit/Loss Tag, set its data type to dimension, and its field type to text.

      Expression breakdown:

      1. Calculate the total profit by order date using the formula: LOD_FIXED{[Order Date]:sum([Profit])}

      2. The CASE statement then checks if this total profit is greater than 0 and returns "Profit" or "Loss" accordingly.

   2. Field 2: Number of Days = count(distinct [Order Date])

   3. Field 3: Month = BI_MONTH([Order Date])

2. Create a chart.

   You can use the split dimension to create small multiples. This technique visualizes data in separate charts using stacked or categorized trends, which helps clarify comparisons.

   In this example, we create a stacked bar chart to show the distribution of profit and loss days as a stacked trend, and two area charts to display the distributions separately.

   1. First, create a stacked bar chart. Drag the Month field to the category axis / dimension area, the Number of Days field to the value axis / measure area, the Daily Profit/Loss Tag field to the color legend / dimension area, and the Order Date (year) field to the split / dimension area. Click Update, and the chart updates automatically.

      

   2. Next, create two area charts, one for loss days and one for profit days.

      In both area charts, drag the Month field to the category axis / dimension area, the Number of Days field to the value axis / measure area, and the Order Date (year) field to the split / dimension area.

      In the filter pane, drag the Daily Profit/Loss Tag field. For the first chart, set the filter to an exact match for "Loss"; for the second, set it to "Profit".

      

      Click Update, and the charts update automatically.

      

You can visually compare the trends of profit and loss days for each year and month.

INCLUDE function use cases

An INCLUDE level of detail expression computes values using the specified dimensions in addition to any dimensions already in the view. The INCLUDE function lets you analyze data at a finer aggregation granularity.

Use case 1: Calculate average sales per customer

Scenario

When analyzing sales performance across different products, you might want to calculate the average sales amount per customer. Use the INCLUDE function to calculate the total order amount for each customer, then apply the average aggregation method to display the result.

Procedure

1. Create a calculated field.

   Field expression: LOD_INCLUDE{[user id]:SUM([order amount])}

   Description: Calculates the total order amount for each customer, grouped by user id.

   

2. Create a chart.

   In this example, we create a crosstab.

   Drag the Order Amount and Total Customer Order Amount fields to the Columns shelf. Drag the Product Type field to the Rows shelf. Set the aggregation method for Total Customer Order Amount to Average, then click Update.

You can now see the average sales amount per customer for different product types.

Use case 2: Calculate average max transaction per sales rep

Scenario

A sales director needs to see the average of the largest transaction amounts achieved by each sales representative, aggregated by region and displayed on a map. In this scenario, a level of detail expression (LOD expression) allows you to visualize data at the regional level and drill down to the sales representative level. This provides a clear view of which sales zones are performing well and which are underperforming, which helps in setting different targets for sales representatives in various regions.

In this example, we use the LOD_INCLUDE function to calculate the average of the maximum transaction amount for each sales representative.

Procedure

1. Create a calculated field.

   Field expression: AVG(LOD_INCLUDE{[sales_name]:MAX([price])})

   Description: Adds the sales representative name as an analysis dimension to the view and calculates the average of the maximum sales amount.

   

2. Create a chart.

   In this example, we create a choropleth map.

   1. Drag the calculated field Average Max Sales per Rep to the Color Saturation/Measure shelf, and drag the area field to the Geographic Area/Dimension shelf.

   2. In the Style -> Block pane, highlight the region with the Maximum Value in red.

      

      Click Update.

      

The map now shows that the East China region has a higher maximum sales amount, while the Northwest and Southwest regions have lower amounts.

Use case 3: Calculate total profit for regions with orders > 500,000

Scenario

Calculate the total profit for regions with order amounts greater than 500,000.

Procedure

1. Create a calculated field.

   CASE WHEN LOD_INCLUDE{[region]:BI_SUM([order amount])}>500000 
   then [profit amount] 
   else 0 
   end

   Expression breakdown:

   1. Calculate the order amount for each region. Name the field Region Order Amount. Formula: LOD_INCLUDE{[region]:BI_SUM([order amount])}

   2. Identify regions with an order amount over 500,000 and calculate their profit amount. Formula: CASE WHEN [Region Order Amount]>500000 then [profit amount] else 0 end

   3. Finally, sum the result to calculate the total profit for regions with an order amount greater than 500,000.

2. Create a chart.

   In this example, we create a card to display the data and a crosstab to verify its accuracy. On the card, drag the newly created field to the Indicator/Measure shelf and set its aggregation method to Sum. On the crosstab, drag the region field to the Rows shelf. Drag the order amount, profit amount, and the newly created field to the Columns shelf, and set their aggregation method to Sum. Click Update.

   

   You can now see that the regions with an order amount greater than 500,000 are Northeast, East China, North China, and South China, with a total profit of 459,000.

Use case 4: Calculate total profit for train shipments > 100,000 by product type

Scenario

This scenario is an extension of Use case 3. It adds a filter for "Train" as the shipping method and groups the results by product type. The goal is to calculate the total profit for regions where the order amount for products shipped by train exceeds 100,000. This involves nesting multiple functions, which are broken down below for clarity.

Procedure

1. Create a calculated field:

   SUM(
   CASE WHEN LOD_INCLUDE{[Product Type],[region]:sum(if([shipping method]='Train',[order amount],0))}>100000
   then [profit amount]
   else 0
   end)

   Expression breakdown:

   1. Calculate the total order amount for train shipments for each product type and region. Name this field Product Type-Region Order Amount. Formula: LOD_INCLUDE{[Product Type],[region]:sum(if([shipping method]='Train',[order amount],0))}

   2. Identify regions with an order amount over 100,000 and return their profit amount. Formula: CASE WHEN [Product Type-Region Order Amount]>100000 then [profit amount] else 0 end

   3. Finally, use the SUM function to calculate the total.

2. Create charts.

   1. Create two cards to display the data. On Card 1, drag the newly created field to the Indicator/Measure shelf. On Card 2, drag the newly created field to the Indicator/Measure shelf and the Product Type field to the Label/Dimension shelf. Click Update.

      

      The results show that the total profit from regions with train shipment order amounts over 100,000 is 426,000. This is broken down by type: Office Supplies at 147,200, Furniture at 0, and Technology at 278,800.

   2. You can also create a crosstab to verify the data's accuracy. Drag Product Type, shipping method, and region to the Rows shelf. Drag order amount, profit amount, and the newly created field to the Columns shelf. Click Update.

      Using Office Supplies as an example, you can see that the regions with train shipment order amounts greater than 100,000 are Northeast, East China, North China, and South China. The corresponding total profit is 147,200, which matches the data on the card.

Use case 5: Count customers for train shipments > 100,000 by product type

Scenario

This scenario is similar to Use case 4, but instead of calculating total profit, it counts the number of customers.

Procedure

1. Create a calculated field:

   COUNT(DISTINCT(
   CASE WHEN LOD_INCLUDE{[Product Type],[region]:sum(if([shipping method]='Train',[order amount],0))}>100000
   then [user id]
   else null
   end))

   Expression breakdown:

   1. Calculate the total order amount for train shipments for each product type and region. Name this field Product Type-Region Order Amount. Formula: LOD_INCLUDE{[Product Type],[region]:sum(if([shipping method]='Train',[order amount],0))}

   2. Identify regions with an order amount over 100,000 and return the corresponding user IDs. Formula: CASE WHEN [Product Type-Region Order Amount]>100000 then [user id] else null end

   3. Finally, use COUNT(DISTINCT()) to count the unique customers.

2. Create a chart.

   In this example, create a card. Drag the newly created field to the Indicator/Measure shelf, and then click Update.The card shows 1,026 customers in regions where train shipment order amounts exceed 100,000.

Use case 6: Count regions with orders > 500,000

Scenario

This scenario is similar to Use case 3, but instead of calculating total profit, it counts the number of qualifying regions.

Procedure

1. Create a calculated field:

   COUNT(DISTINCT(
   CASE WHEN LOD_INCLUDE{[region]:sum([order amount])}>500000
   then [region]
   else null
   end))

   Expression breakdown:

   1. Calculate the order amount for each region. Name this field Region Order Amount. Formula: LOD_INCLUDE{[region]:sum([order amount])}

   2. Identify regions with an order amount over 500,000. Formula: CASE WHEN [Region Order Amount]>500000 then [region] else null end

   3. Finally, use COUNT(DISTINCT()) to count the unique regions.

2. Create a chart.

   In this example, we create a card to display the data and a crosstab to verify its accuracy. On the card, drag the newly created field to the Indicator/Measure shelf. On the crosstab, drag the region field to the Rows shelf and the order amount field to the Columns shelf. Click Update.

   

   You can now see that there are 4 regions with an order amount greater than 500,000: Northeast, East China, North China, and South China.

Use case 7: Calculate average 2024 sales per province by product type

Scenario

Calculate the average sales amount per province for each product type in 2024.

Procedure

1. Create a calculated field.

   AVG(
   LOD_INCLUDE{[Product Type],[province]:
   SUM(IF(YEAR([Order Date])=2024,[order amount],0))
   }
   )

   Expression breakdown:

   1. Calculate the order amount for orders placed in 2024. Name this field 2024 Order Amount. Formula: IF(YEAR([Order Date])=2024,[order amount],0)

   2. Calculate the sales amount by product type and province. Formula: LOD_INCLUDE{[Product Type],[province]:SUM([2024 Order Amount])}

   3. Use the AVG function to calculate the average.

2. Create charts.

   In this example, we create a card to display the data and a crosstab to verify its accuracy. On the card, drag the newly created field to the Indicator/Measure shelf and the Product Type field to the Label/Dimension shelf. On the crosstab, drag the Product Type field to the Rows shelf and the order amount field to the Columns shelf, and filter the data for the year 2024. Click Update.

   

   You can now see that the order amount for Office Supplies in 2024 is 235,600. The average sales amount per province is 235,600 / 31 provinces = 7,600. The same calculation applies to other product types.

Use case 8: Calculate average 2024 customers per province by product type

Scenario

This scenario is similar to Use case 7, but it calculates the average number of customers instead of the average sales amount.

Procedure

1. Create a calculated field.

   AVG(
   LOD_INCLUDE{[Product Type],[province]:
   COUNT(DISTINCT(IF(YEAR([Order Date])=2024,[user id],null)))
   }
   )

   Expression breakdown:

   1. Find the user IDs for orders placed in 2024. Name this field 2024 Customer Count. Formula: IF(YEAR([Order Date])=2024,[user id],null)

   2. Calculate the number of customers by product type and province. Formula: LOD_INCLUDE{[Product Type],[province]:SUM([2024 Customer Count])}

   3. Use the AVG function to find the average.

2. Create charts.

   In this example, we create a card to display the data and a crosstab to verify its accuracy. On the card, drag the newly created field to the Indicator/Measure shelf and the Product Type field to the Label/Dimension shelf. On the crosstab, drag the Product Type field to the Rows shelf and the Customers per Province by Product Type field to the Columns shelf, and filter the data for the year 2024. Click Update.

   Customers per Province by Product Type = LOD_INCLUDE{[Product Type],[province]:COUNT(DISTINCT([user id]))}

   

   You can now see that the number of customers for Office Supplies in 2024 is 212. The average number of customers per province is 212 / 31 provinces ≈ 6.84. The same calculation applies to other product types.

Use case 9: Calculate product profit target gap and completion rate

Scenario

Once profit target performance is clear at the province level, you can perform a deeper analysis to see which specific products met their targets and which did not.

This involves calculating the profit target gap for each product within a province, counting the number of products that met their target, and calculating the percentage of target-meeting products.

This scenario requires building two charts and enabling interaction between them. Clicking on the product statistics chart will filter the detailed product view.

Procedure

1. Create calculated fields.

   1. Field 1: Product Profit Target Gap

      LOD_INCLUDE{[Product] : sum([profit amount]-[profit target])}

      Description: Calculates the profit target gap for each product.

   2. Field 2: Percentage of Products Meeting Target

      count(distinct 
      case when [Product Profit Target Gap]>0 
      then [Product] 
      else null end)
      /count(distinct [Product])

      Expression breakdown:

      1. Perform a distinct count of products where the profit target gap is greater than 0 to find the Number of Products Meeting Target. Formula: count(distinct case when [Product Profit Target Gap]>0 then [Product] else null end)

      2. Perform a distinct count of all products. Formula: count(distinct [Product])

      3. Divide the number of products meeting the target by the total number of products to calculate the Percentage of Products Meeting Target.

2. Create charts.

   In this example, we need to create two charts and enable interaction.

   1. First, create a bar chart to show the Product Profit Target Gap. Drag the Product field to the Category/Dimension shelf and the Product Profit Target Gap field to the Value/Measure shelf. Click Update.

      

   2. Next, create another bar chart to show the Percentage of Products Meeting Target. Drag the province field to the Category/Dimension shelf and the Percentage of Products Meeting Target field to the Value/Measure shelf. Click Update.

      

   3. Set up interaction between the two bar charts.

      Note

      In this example, both bar charts use the same dataset. If automatic interaction is enabled for the dashboard, the charts will link automatically without manual configuration. If not, you can configure interaction manually.

      For detailed instructions, see Interaction.

      Interaction demonstration.

You can now see the profit target completion status for each province and view the specific products that have met or missed their targets.

Use case 10: Evaluate and compare store performance by region

Scenario

Based on detailed sales and gross profit data for each store, you can calculate the total and average sales and gross profit for different regional groupings (e.g., major and sub-regions). By filtering on these dimensions, you can evaluate and compare store performance across regions.

Procedure

1. Create calculated fields.

   1. Field 1: Store Gross Profit = LOD_INCLUDE{[Store Name]:sum([gross profit])}

   2. Field 2: Store Sales Amount = LOD_INCLUDE{[Store Name]:sum([sales amount])}

2. Create a chart.

   In this example, we create a crosstab and configure conditional formatting.

   1. On the crosstab, drag Major Region and Sub-region to the Rows shelf. In the Columns shelf, drag the Store Gross Profit field twice, setting one to Average and the other to Sum. Do the same for the Store Sales Amount field.

      

   2. Rename the column headers. Rename "Store Gross Profit (Average)" and "Store Sales Amount (Average)" to Average Per Store. Rename "Store Gross Profit (Sum)" and "Store Sales Amount (Sum)" to Total.

   3. In the Style -> Cell -> Measure Display Grouping pane, set up the Measure Grouping.

      

   4. To improve the visual presentation, configure conditional formatting. In this example, the conditional formatting for the four column fields is configured as follows:

   5. Click Update.

You can now clearly see the total and average per store sales amount and gross profit for each region.

Use case 11: Evaluate regions by provincial performance

Scenario

For each region, calculate the number of provinces where the sales amount exceeds a specified threshold. For these qualifying provinces, calculate the total number of customers and the average number of customers per province. The threshold can be changed dynamically using a filter.

Procedure

1. Create calculated fields.

   1. Field 1: Number of Qualifying Provinces

      count(distinct 
      case when LOD_INCLUDE{[province]:sum([order amount])} > $val{ord_amt_level} 
      then [province] 
      else null
      end)

      Expression breakdown:

      1. Calculate the order amount for each province. Name the field Province Order Amount. Formula: LOD_INCLUDE{[province]:sum([order amount])}

      2. This expression returns the province name if its Province Order Amount exceeds the ord_amt_level value placeholder. Name the field Qualifying Province. Formula: case when [Province Order Amount] > $val{ord_amt_level} then [province] else null end

      3. Count the unique qualifying provinces. Formula: count(distinct [Qualifying Province])

   2. Field 2: Total Customers in Qualifying Provinces

      count(distinct 
      case when lod_include{[province]:sum([order amount])} > $val{ord_amt_level} 
      then [customer id] 
      else null 
      end)

      Expression breakdown:

      1. Calculate the order amount for each province. Name the field Province Order Amount. Formula: LOD_INCLUDE{[province]:sum([order amount])}

      2. Return the customer ID if the Province Order Amount is greater than the ord_amt_level value placeholder. Name the field Customers in Qualifying Provinces. Formula: case when [Province Order Amount] > $val{ord_amt_level} then [customer id] else null end

      3. Count the unique customers. Formula: count(distinct [Customers in Qualifying Provinces])

   3. Field 3: Average Customers per Province. This is calculated by dividing Total Customers in Qualifying Provinces (Field 2) by Number of Qualifying Provinces (Field 1).

2. Create a chart.

   1. Create a crosstab. Drag the region field to the Rows shelf. Drag the Number of Qualifying Provinces, Total Customers in Qualifying Provinces, and Average Customers per Province fields to the Columns shelf.

   2. Insert an in-chart query condition. Set the query condition to Province Sales Amount, link it to the ord_amt_level value placeholder, and set the default value to 100000.

   3. Click Update.

      

You can now enter different province sales amounts to filter the view and observe how the data changes.

EXCLUDE function applications

An EXCLUDE level of detail expression removes specified dimensions from a calculation.

Use case 1: Calculate province sales percentage

Scenario

To calculate the sales percentage of each province within a region, you need the region's total sales. Use the EXCLUDE function to compute the sales amount for the region by excluding the province dimension.

Procedure

1. Create a calculated field.

   Field expression: LOD_EXCLUDE{[province]:SUM([Order Amount])}

   Description: This expression calculates the regional sales amount by excluding the province dimension from the calculation.

   

2. Create a chart.

   In this example, create a crosstab. Drag the Order Amount and Total Sales in the Region fields to columns, and drag the Region and Province fields to rows. Click update to automatically refresh the chart.

   

The crosstab now shows the order amount for each province alongside the total sales for its region.

Use case 2: Difference between region and theater average

Scenario

To identify high- and low-performing sales regions, you can calculate the difference between each region's average profit and the average profit of its parent theater. This report uses a level of detail (LOD) expression and conditional formatting to highlight these differences.

In this example, we use the LOD_EXCLUDE function to calculate the difference between each sales region's average and the theater's average.

Procedure

1. Create a calculated field.

   1. Field expression: AVG(LOD_EXCLUDE{[province]:AVG([price])})

      Description: This expression removes the province dimension from the aggregation granularity to calculate the average sales amount for each theater (such as East China).

   2. Subtracting the LOD result from AVG([price]) returns the difference between each sales region's average and the theater's average: AVG([price]) - AVG(LOD_EXCLUDE{[province]:AVG([price])}).

      

2. Create a chart.

   In this example, create a crosstab.

   1. Drag the Provincial Average Difference field to columns, and drag the area and province fields to rows.

   2. In the Style > conditional formatting section, configure the field to display values greater than 0 in red and values less than 0 in green.

      

   3. Click update, and the chart updates automatically.

      

The results show that in the East China theater, Shanghai, Anhui, Jiangsu, and Fujian are positive, with Shanghai performing best. In contrast, Shandong, Jiangxi, and Zhejiang are below average.

Filter condition rules

LOD_FIXED function with external filter conditions

• Scenario: Calculation unaffected by filters

  LOD_FIXED_1 field expression: LOD_FIXED{[region]: SUM([order amount])}

  Filter condition: transportation method = "Truck"

  Result: As shown in the following figure, the total order amount for the Northeast region is 527,400. Applying the external filter transportation method = "Truck" does not change this result.

  Conclusion: When the filter condition's dimension differs from the LOD function's aggregation dimension, the filter condition does not affect the final result.

  

• Scenario: Calculation affected by filters

  LOD_FIXED_2 field expression: LOD_FIXED{[region], [product type], [transportation method]: SUM([order amount])}

  Filter condition: transportation method = "Truck"

  Result: As shown in the following figure, the total order amount for Office Supplies in the Northeast region is 150,800. This is the sum of three transportation methods: Truck (35,540), Train (103,100), and Air (12,110). Applying an external filter of transportation method = "Truck" changes the order amount for Office Supplies in the Northeast region to 35,540, which represents only Truck shipments.

  Conclusion: If the filter condition uses the same aggregation dimension as the LOD function, a secondary aggregation occurs, and the filter condition affects the final result.

  

LOD functions with internal filter conditions

Conclusion: When an internal filter condition has the same aggregation granularity as the LOD function, the function filters the relevant data accordingly. Otherwise, the filter applies only to the LOD field.

• LOD_FIXED function

  • LOD_FIXED_3 field expression: LOD_FIXED{[region], [product type], [transportation method]: SUM([order amount]): [order level]='Medium'}

    Result: As shown in the following figure, the order amount for Office Supplies shipped by Truck in the Northeast region is 35,070. This amount results from filtering for the 'Medium' order level and therefore only includes orders that match this criterion.

    

  • LOD_FIXED_4 field expression: LOD_FIXED{[region], [product type], [transportation method]: SUM([order amount]): [transportation method]='Truck'}

    Result: As shown in the following figure, the order amount for Office Supplies shipped by Truck in the Northeast region is 35,540. When the internal filter condition matches the aggregation granularity of the LOD function, the LOD_FIXED function filters for the 'Truck' transportation method.

    

• LOD_INCLUDE function

  The LOD_EXCLUDE and LOD_INCLUDE functions follow the same logic. This section uses the LOD_INCLUDE function as an example.

  

  • LOD_INCLUDE_1 field expression: LOD_INCLUDE{: SUM([order amount]): [order level]='Medium'}

    Result: As shown in the following figure, the order amount for Office Supplies shipped by Truck in the Northeast region is 35,070. This amount results from filtering for the 'Medium' order level and therefore only includes orders that match this criterion.

  • LOD_INCLUDE_2 field expression: LOD_INCLUDE{: SUM([order amount]): [transportation method]='Truck'}

    Result: As shown in the following figure, the order amount for Office Supplies shipped by Truck in the Northeast region is 35,540. When the internal filter condition matches the aggregation granularity of the LOD function, the LOD_INCLUDE function filters for the 'Truck' transportation method.