Quick BI:Analytical functions (LOD functions)

Limits

Detail tables do not support LOD functions.

Background

Analysis in Quick BI primarily uses fixed dimensions. For example, to view the number of orders by region and province, you can place the region and province dimensions on the rows of a cross table. Then, you can place the order count measure on the columns and set the aggregation method to sum.

The generated SQL query is as follows:

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 see data at both the region-province granularity and the region-only granularity in the same table? Or what if you want to see the ratio or difference between province data and the corresponding region data? Or what if you want to find the product type with the highest sales amount in each province? To handle these scenarios, you need to use LOD functions.

Scenarios

A Level of Detail (LOD) expression lets you manage multiple levels of data detail in a single visualization. The level of detail refers to the granularity at which data is aggregated. LOD expressions are useful for scenarios where a visualization needs to display data at multiple levels of data detail simultaneously.

You can use an LOD expression to add a dimension with a higher or lower level of detail than the current view without changing the visualization.

Syntax

LOD, or Level of Detail expressions, are a powerful calculation feature. They allow you to create complex calculations and aggregations in your data analysis. LOD expressions help you control the granularity of calculations. You can analyze data at a specified level (FIXED), a more granular level (INCLUDE), or a less granular level (EXCLUDE). The basic syntax is as follows:

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 box, select the required LOD function and fields (②).

   

3. After the field is created, click OK. When you use the new field to create a dashboard chart, the total order amount for the same region does not change with the product type.

   

Expression details

Basic formulas

The structure and syntax for the three types of LOD expressions are as follows:

• 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 details

  • FIXED | INCLUDE | EXCLUDE: Scoping keyword that specifies the scope of the LOD.

  • <Dimension declaration>: Specifies one or more dimensions to which the aggregate expression relates. Use commas to separate multiple dimensions.

  • <Aggregate expression>: The calculation that is performed at the specified level of detail.

Filter conditions

In Quick BI, in addition to basic formulas, you can also write filter conditions. The expression is as follows, with the dimension declaration, aggregate expression, and filter condition separated by colons:

• LOD_FIXED{Dimension1,Dimension2...:Aggregate expression:Filter condition}

• LOD_INCLUDE{Dimension1,Dimension2...:Aggregate expression:Filter condition}

• LOD_EXCLUDE{Dimension1,Dimension2...:Aggregate expression:Filter condition}

How it works

In practice, the aggregate data generated by an LOD expression is often merged with raw data or data at other aggregation levels to create a richer data view. In SQL, this typically requires subqueries and JOIN operations. This section describes how FIXED, INCLUDE, and EXCLUDE level LOD expressions work.

FIXED level

Recall the scenario from the background information: you want to view data at both the region-province granularity and the region-only granularity in the same table.

In this case, you can create a FIXED expression:

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

In SQL, you can achieve this result with the following query. This query creates a subquery to calculate the number of orders for each region and then joins the result of the subquery with the original 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 see data at the region granularity and also see the proportion of the province with the largest order volume within that region in the same table?

In this case, you can create an INCLUDE expression:

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

In SQL, you can achieve this result with the following query. This query creates a subquery to calculate the number of orders for each region-province combination and then joins the result of the subquery with the original 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

To view data at both the region-province and region-only granularities in the same table, you can use a FIXED level LOD expression to specify the 'area' dimension for aggregation. Alternatively, you can use an EXCLUDE level LOD expression. Because the chart already includes the region and province dimensions, you can obtain the same result by excluding the 'province' dimension before aggregating. This is the logic behind the EXCLUDE level LOD expression.

In this case, you can create an EXCLUDE expression:

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

In SQL, you can achieve this result with the following query. This query creates a subquery to calculate the number of orders for each region and then joins the result of the subquery with the original query:

-- Simplified structure
-- lod_EXCLUDE{[province]:SUM([order_number])}
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_number
    LOD_TP_0.`LOD_measure_result` AS LOD_3      -- lod_EXCLUDE{[province]:SUM([order_number])}
FROM
    (
        SELECT      -- Main query: area, province, sum(order_number)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            SUM(ADR_T_1_.`order_number`) 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_number)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            SUM(ADR_T_1_.`order_number`) 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_number])}
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 applications

A FIXED Level of Detail expression computes a value using the specified dimensions, independent of the dimensions in the view.

Application scenario 1: Calculate the total sales amount for each region

Scenario description

When analyzing sales orders by geographical distribution, your data table contains the Region and Province dimensions. You can use a FIXED expression to calculate the total amount for each Region. A FIXED LOD expression calculates a value based only on the dimensions specified in the expression, ignoring other dimensions in the view. This allows it to calculate the total sales amount for the corresponding region regardless of the province-level detail.

Procedure

1. Create a calculated field.

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

   Meaning: Calculates the sum of the order amount by region.

   

2. Create a chart.

   In this example, create a cross table.

   Drag the Total Amount by Region field created in the previous step to the Columns area. Drag the Region and Province fields to the Rows area. Click Update. The system automatically updates the chart.

   

   Now, you can see that the total amount for the same region is consistent and is not affected by the province.

Application scenario 2: Customer order frequency

Scenario description

A sales manager wants to know the number of customers who have placed one, two, three, or more orders. By examining the number and distribution of customer purchase frequencies, the manager can analyze customer loyalty and repeat purchases. In this scenario, you can use an LOD expression to segment one measure by another.

In this example, use the LOD_FIXED function to convert the order count into a dimension based on the number of customers to obtain the customer order frequency.

Procedure

1. Create a calculated field.

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

   Meaning: Calculates the number of purchases for each customer based on their name.

2. Create a chart.

   In this example, create a column chart.

   Drag the Number of Purchases field created in the previous step to the Category Axis/Dimensions area. Drag the customer_name field to the Value Axis/Measures area and set it to Count Distinct. Click Update. The system automatically updates the chart.

   

Now, you can see that the highest number of customers made 7 purchases, and one customer made as many as 58 purchases.

Application scenario 3: Profit percentage leaderboard by region

Scenario description

A regional sales director wants to know the contribution of each region to the total profit and see at a glance which regions are the top contributors. For this scenario, you can use Advanced Calculation -> Percentage of Total, or you can use an LOD expression for more flexibility.

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

Procedure

1. Create a calculated field.

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

      Meaning: In this example, the FIXED syntax is unique. The expression does not specify any dimensions. This means it aggregates the total profit amount across the entire dataset.

   2. Then, divide SUM([profit_amt]) by the previous LOD function, as shown in the expression: SUM([profit_amt]) / SUM(LOD_FIXED{:SUM([profit_amt])}). This calculates the profit percentage for each region.

      

2. Create a chart.

   In this example, create a leaderboard.

   Drag the Total Profit Percentage field created in the previous step to the Indicators/Measures area. Drag the area field to the Category/Dimensions area. Click Update. The system automatically updates the chart.

   

Now, you can see that South China and North China are the top two contributors. East China? and Southwest China have the lowest contributions, with negative profit amounts. The 'East China?' data appears to be dirty data.

Application scenario 4: Annual new user statistics

Scenario description

How can you determine if your product is growing? Besides the usual PV (Page View) and UV (Unique Visitor) metrics, you can also track user loyalty. For example, if users who have been with you for a long time continue to use your product and make purchases, you can consider your product to be sticky. To achieve this, you can use an LOD expression.

In this example, use the LOD_FIXED function to create annual new user statistics.

Procedure

1. Create a calculated field.

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

   Meaning: Finds the earliest order date for each customer at the year granularity. You can adjust the granularity as needed using a date function that is compatible with your database syntax.

2. Create a chart.

   In this example, create a line chart.

   Drag the Customer's First Purchase Year field created in the previous step to the Category Axis/Dimensions area. Drag Sales Amount to the Value Axis/Measures area. Click Update. The system automatically updates the chart.

   

Now, you can see that users who started in 2013 still contribute significantly, which indicates high product stickiness.

Application scenario 5: Analyze the trend of new customers per year based on order details

Scenario description

Calculate the earliest purchase year for each customer and 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 the data type to Dimension and the field type to Text.

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

2. Create a chart.

   In this example, create a column chart to display the data. Drag the Customer First Purchase Year field to the Category Axis/Dimensions area. Drag the Number of Customers field to the Value Axis/Measures area. Click Update. The system automatically updates the chart.

   

Now, you can see the number of new customers for each year. The number peaked in 2021 and has declined significantly since then.

Application scenario 6: Analyze the distribution of customers by purchase frequency based on order details

Scenario description

Calculate the purchase frequency for each customer and then analyze the distribution of the number 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 the data type to Dimension and the field type to Text.

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

2. Create a chart.

   In this example, create a column chart to display the data. Drag the Customer Purchase Frequency field to the Category Axis/Dimensions area. Drag the Number of Customers field to the Value Axis/Measures area. Click Update. The system automatically updates the chart.

   

Now, you can see that the largest number of customers have a purchase frequency of 3.

Application scenario 7: Analyze annual order amounts and compare them with the key year 2023

Scenario description

Analyze data within a fixed range and make comparisons. For example, analyze annual order data and compare it with the key year 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

   Field breakdown:

   1. Calculate the order amount for 2023. Formula: LOD_FIXED{:sum(case when BI_YEAR([Order Date]) ='2023' then [Order amount] else 0 end)}

   2. Calculate the order amount for each year and compare it with the order amount for 2023. Formula: sum([Order amount])/[2023 Order Amount]-1. Name the field Comparison with 2023.

2. Create a chart.

   In this example, create a cross table. 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. The system automatically updates the chart.

   

Now, you can see the order amount for each order year and its comparison with 2023.

Application scenario 8: Analyze the number of profitable and unprofitable days per month for each year

Scenario description

Based on daily profit statistics, tag each day as profitable or unprofitable. Then, count the number of days based on the profit/loss tag, order year, and order month.

Procedure

1. Create calculated fields.

   1. Field 1:

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

      Name this field Daily Profit/Loss Tag. Set the data type to Dimension and the field type to Text.

      Field breakdown:

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

      2. If the total profit is greater than 0, return "Profitable". Otherwise, return "Unprofitable". Formula: case when [Total Profit]>0 then 'Profitable' else 'Unprofitable' end

   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 of a column-line chart to create small multiples. Visualize the data using stacked trends or categorical trends to clearly see comparisons and trends.

   In this example, create a stacked column chart to show the distribution of profitable and unprofitable days as a stacked trend, and two area charts to show the distribution of profitable and unprofitable days separately.

   1. First, create a stacked column chart. Drag the Month field to the Category Axis/Dimensions area. Drag the Number of Days field to the Value Axis/Measures area. Drag the Daily Profit/Loss Tag field to the Color Legend/Dimensions area. Drag the Order Date (year) field to the Split/Dimensions area. Click Update. The system automatically updates the chart.

      

   2. Next, create two area charts, one for unprofitable days and one for profitable days.

      In both area charts, drag the Month field to the Category Axis/Dimensions area. Drag the Number of Days field to the Value Axis/Measures area. Drag the Order Date (year) field to the Split/Dimensions area.

      In the filter, drag the Daily Profit/Loss Tag field. For the unprofitable days chart, set the filter condition to an exact match for "Unprofitable". For the profitable days chart, set the filter condition to an exact match for "Profitable".

      

      Click Update. The system automatically updates the charts.

      

Now, you can visually compare the trends of profitable and unprofitable days for each month of each year.

INCLUDE function applications

An INCLUDE Level of Detail expression computes a value using the dimensions in the view plus any dimensions specified in the expression. The INCLUDE function adds a more granular level of detail to the calculation for deeper analysis.

Application scenario 1: Calculate the average sales per customer

Scenario description

When analyzing the sales performance of various products, you need to view the average sales per customer. You can use INCLUDE to first calculate the total order sales amount for each customer, and then display the result using the Average aggregation method.

Procedure

1. Create a calculated field.

   Field expression: LOD_INCLUDE{[User ID]:SUM([Order amount])}

   Meaning: Calculates the total order amount for each customer by user ID.

   

2. Create a chart.

   In this example, create a cross table.

   Drag the Order amount and Total Customer Order Amount fields to the Columns area. Drag the Product Type field to the Rows area. Set the aggregation method for Total Customer Order Amount to Average. Click Update. The system automatically updates the chart.

Now, you can see the average sales per customer for different product types.

Application scenario 2: Average maximum transaction amount for each sales representative

Scenario description

A sales director needs to know the average of the maximum transaction amounts achieved by each sales representative, calculated by region, and display it on a map. In this scenario, you can use an LOD expression to display data intuitively at the regional level. You can also drill down to the sales representative level to see which sales zones are performing well and which are underperforming. This helps in setting different goals for sales representatives in various regions.

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

Procedure

1. Create a calculated field.

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

   Meaning: Adds the sales representative's name as an additional analysis granularity to the existing view and calculates the average of the maximum sales amounts.

   

2. Create a chart.

   In this example, create a colored map.

   1. Drag the Average Maximum Sales Amount per Sales Rep by Region field created in the previous step to the Color Saturation/Measures area. Drag the area field to the Geographic Area Axis/Dimensions area.

   2. In Style -> Block, mark the region with the Maximum value for this field in red.

      

      Click Update. The system automatically updates the chart.

      

Now, you can see that the maximum sales amount is larger in the East China region and smaller in the Northwest/Southwest regions.

Application scenario 3: Calculate the total profit for regions with an order amount greater than 500,000

Scenario description

Calculate the order amount for each region and find the total profit for regions where the order amount exceeds 500,000.

Procedure

1. Create a calculated field.

   CASE WHEN LOD_INCLUDE{[Region]:BI_SUM([Order amount])}>500000 
   then [Profit Amount] 
   else 0 
   end

   Field breakdown:

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

   2. Find regions where the order amount exceeds 500,000 and calculate their profit amount. Formula: CASE WHEN [Region Order Amount]>500000 then [Profit Amount] else 0 end

   3. Finally, sum the results (using SUM or by setting the field's aggregation method in the chart to Sum) to obtain the total profit for regions with an order amount greater than 500,000.

2. Create a chart.

   In this example, create a kanban to display the data. You can also create a cross table to verify the accuracy of the data. In the kanban, drag the newly created field to the Kanban Indicators/Measures area and set the aggregation method to Sum. In the cross table, drag Region to the Rows area, and drag Order amount, Profit Amount, and the newly created field to the Columns area. Set the aggregation method to Sum. Click Update. The system automatically updates the chart.

   

   Now, you can see that the regions with an order amount greater than 500,000 are Northeast, East China, North China, and South China. Their total profit is 1.48M. .

Application scenario 4: Calculate the total profit for regions where the order amount for train-shipped products exceeds 100,000, grouped by product type

Scenario description

This is an advanced version of Application scenario 3. It filters for products shipped by train and adds product type as a grouping. The goal is to calculate the total profit for regions where the order amount for train-shipped products exceeds 100,000, grouped by product type. This scenario involves nesting multiple functions. To make it easier to understand, it can be broken down.

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)

   Field breakdown:

   1. Calculate the total order amount for train-shipped products for each product type, summarized by region. Name the field Product Type-Region Order Amount. Formula: LOD_INCLUDE{[Product Type],[Region]:sum(if([Shipping Method]='Train',[Order amount],0))}

   2. Find regions where the order amount exceeds 100,000 and calculate the total profit. Formula: CASE WHEN [Product Type-Region Order Amount]>100000 then [Profit Amount] else 0 end

   3. Finally, use SUM to obtain the total.

2. Create a chart.

   1. Create two kanbans to display the data. In Kanban 1, drag the newly created field to the Kanban Indicators/Measures area. In Kanban 2, drag the newly created field to the Kanban Indicators/Measures area and drag Product Type to the Kanban Labels/Dimensions area. Click Update. The system automatically updates the charts.

      

      Now, you can see that the total profit for regions where the order amount for train-shipped products exceeds 100,000 is is 3.1M, office supplies are 1.23M, furniture supplies are 445K, and technical supplies are 1.41M.

Application scenario 5: Calculate the number of customers in regions where the order amount for train-shipped products exceeds 100,000, grouped by product type

Scenario description

This scenario is similar to Application scenario 4, but instead of calculating the total profit, it calculates 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))

   Field breakdown:

   1. Calculate the total order amount for train-shipped products for each product type, summarized by region. Name the field Product Type-Region Order Amount. Formula: LOD_INCLUDE{[Product Type],[Region]:sum(if([Shipping Method]='Train',[Order amount],0))}

   2. Find regions where the order amount exceeds 100,000 and identify the corresponding customers. 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 kanban. Drag the newly created field from the previous step to the Kanban Indicators/Measures area. Click Update. The system automatically updates the chart.Now, you can see that the number of customers in regions where the order amount for train-shipped products exceeds 100,000 is 1,026.

Application scenario 6: Calculate the number of regions with an order amount greater than 500,000

Scenario description

This scenario is similar to Application scenario 3, but instead of calculating the total profit, it calculates the number of regions.

Procedure

1. Create a calculated field:

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

   Field breakdown:

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

   2. Find regions where the order amount exceeds 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, create a kanban to display the data. You can also create a cross table to verify the accuracy. In the kanban, drag the newly created field to the Kanban Indicators/Measures area. In the cross table, drag Region to the Rows area and Order amount to the Columns area. Click Update. The system automatically updates the chart.

   

   Now, you can see that there are 3 regions with an order amount greater than 500,000: South China, East China,, and North China.

Application scenario 7: Calculate the average sales per province for each product type in 2024

Scenario description

Calculate the average sales 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))
   }
   )

   Field breakdown:

   1. Calculate the order amount for the year 2024. Name the 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 AVG to find the average.

2. Create a chart.

   In this example, create a kanban to display the data. You can also create a cross table to verify the accuracy. In the kanban, drag the newly created field to the Kanban Indicators/Measures area and Product Type to the Kanban Labels/Dimensions area. In the cross table, drag Product Type to the Rows area and Order amount to the Columns area, and filter for data from 2024. Click Update. The system automatically updates the chart.

   

   Now, you can see that the order amount of office supplies in 2024 is 10.7M, and the average sales volume of provinces is 10.7M/30 provinces, that is, 357K. Other product types are calculated in the same way.

Application scenario 8: Calculate the average number of customers per province for each product type in 2024

Scenario description

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

Procedure

1. Create a calculated field.

   AVG(
   LOD_INCLUDE{[Product Type],[Province]:
   COUNT(DISTINCT(IF(YEAR([Order Date])='2024',[User ID],null)))
   }
   )

   Field breakdown:

   1. Count the number of customers for the year 2024. Name the field 2024 Customer Count. Formula: IF(YEAR([Order Date])='2024',[User ID],null)

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

   3. Use AVG to find the average.

2. Create a chart.

   In this example, create a kanban to display the data. You can also create a cross table to verify the accuracy. In the kanban, drag the newly created field to the Kanban Indicators/Measures area and Product Type to the Kanban Labels/Dimensions area. In the cross table, drag Product Type to the Rows area and Customer Count per Province by Product Type to the Columns area, and filter for data from 2024. Click Update. The system automatically updates the chart.

   Customer Count per Province by Product Type=LOD_INCLUDE{[Product Type],[Province]:COUNT(DISTINCT([User ID]))}

   

   Now, you can see that in 2024, the number of customers for office supplies in the province is 212, and the average number of customers in the province is 212/31 provinces, that is, 6.839. Other product types are calculated in the same way.

Application scenario 9: Calculate the percentage of products meeting their profit target in each province and the profit gap for each product

Scenario description

When the completion of provincial profit targets is known, you can further analyze which specific products have met their targets and which have not.

This means calculating the profit target gap for each product at the province level, counting the number of products that met the target and the total number of products, and then calculating the percentage.

This scenario requires building two charts and setting up a filter interaction. You can click the product statistics data on the left to view the details of specific products on the right.

Procedure

1. Create calculated fields.

   1. Field 1: Product Profit Target Gap

      LOD_INCLUDE{[Product] : sum([Profit]-[Profit Target])}

      Meaning: Calculates the profit target gap for each product by product type.

   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])

      Field breakdown:

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

      2. Count the unique number of Total Number of Products. Formula: count(distinct [Product])

      3. Divide the two fields above, [Number of Products Meeting Target]/[Total Number of Products], to obtain the Percentage of Products Meeting Target.

2. Create a chart.

   In this example, create two charts and set up a filter interaction.

   1. First, create a bar chart to show the Product Profit Target Gap. Drag the Product field to the Category Axis/Dimensions area. Drag the Product Profit Target Gap field to the Value Axis/Measures area. Click Update. The system automatically updates the chart.

      

   2. Next, create another bar chart to show the Percentage of Products Meeting Target. Drag the Province field to the Category Axis/Dimensions area. Drag the Percentage of Products Meeting Target field to the Value Axis/Measures area. Click Update. The system automatically updates the chart.

      

   3. Set up a filter interaction between the two bar charts.

      Note

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

      For more information, see Filter interaction.

      The filter interaction works as follows.

Now, you can see the completion status of provincial profit targets and view the specific products that have met or missed their targets in each province.

Application scenario 10: Evaluate and compare the performance of outlets in different regions

Scenario description

Based on the detailed data of outlet sales and gross profit, calculate the total sales and gross profit for major regions and sub-regions. Also, calculate the average sales and gross profit per outlet within the corresponding region. You can also use a filter dimension to view the performance of outlets at the major region and sub-region levels, or just the major region level, to evaluate and compare the performance of outlets in different regions.

Procedure

1. Create calculated fields.

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

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

2. Create a chart.

   In this example, create a cross table and configure conditional formatting.

   1. In the cross table, drag Major Region and Sub-region to the Rows area. In the Columns area, drag two Outlet Gross Profit fields, setting one to Average and the other to Sum. Drag two Outlet Sales Amount fields, setting one to Average and the other to Sum.

      

   2. Display the content of the Outlet Gross Profit (Average) and Outlet Sales Amount (Average) fields as Per Outlet. Display the content of the Outlet Gross Profit (Sum) and Outlet Sales Amount (Sum) fields as Total.

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

      

   4. For a better visual presentation, you can configure conditional formatting. In this example, the conditional formatting for the four column fields is as follows:

   5. Click Update. The system automatically updates the chart.

Now, you can intuitively see the total and per-outlet sales and gross profit for each region.

Application scenario 11: Evaluate regions based on the performance of their provinces

Scenario description

Within each region, calculate the total sales by province. Count the number of provinces where the sales exceed a threshold (based on a filter condition input). Also, find the number of customers in those provinces and the average number of customers per province. You can view the data results by entering different threshold values.

Procedure

1. Create calculated fields.

   1. Field 1: Number of Qualified Provinces

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

      Field breakdown:

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

      2. When Province Order Amount is greater than the value placeholder ord_amt_level, return the province. Otherwise, return null. Formula: case when [Province Order Amount ]> $val{ord_amt_level} then [Province] else null end. Name the field Qualified Provinces.

      3. Count the number of unique qualified provinces. Formula: count(distinct [Qualified Provinces])

   2. Field 2: Total Customers in Provinces

      count(distinct 
      case when lod_include{[Province]:sum([Order amount])} > $val{ord_amt_level} 
      then [Customer ID] 
      else null 
      end)

      Field breakdown:

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

      2. When Province Order Amount is greater than the value placeholder ord_amt_level, return the customer ID. Otherwise, return null. Formula: case when [Province Order Amount ]> $val{ord_amt_level} then [Customer ID] else null end. Name the field Customers in Provinces.

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

   3. Field 3: Average Customers per Province=[Total Customers in Provinces]/[Number of Qualified Provinces], which is Field 2 divided by Field 1.

2. Create a chart.

   1. Create a cross table. Drag Region to the Rows area. Drag Number of Provinces, Number of Customers in Provinces, and Average Customers per Province to the Columns area.

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

   3. Click Update. The system automatically updates the chart.

      

Now, you can enter different province sales amounts to filter and view the data changes.

EXCLUDE function applications

An EXCLUDE Level of Detail expression computes a value by removing the dimensions specified in the expression from the view's level of detail.

Application scenario 1: Calculate the sales proportion of each province within a region

Scenario description

When analyzing the sales data of each province within a region, you may also want to view the total sales data for that region and the sales proportion of the province relative to the region. You can use the EXCLUDE function to remove the Province dimension from the calculation, which lets you calculate the total sales amount for the entire region.

Procedure

1. Create a calculated field.

   Field expression: LOD_EXCLUDE{[Province]:SUM([Order amount])}

   Meaning: Calculates the sales amount at the region level by excluding the province dimension from the calculation.

   

2. Create a chart.

   In this example, create a cross table. Drag the Order amount and Total Regional Sales fields to the Columns area. Drag the Region and Province fields to the Rows area. Click Update. The system automatically updates the chart.

   

Now, you can see both the order amount for each province and the total sales amount for the corresponding region.

Application scenario 2: Calculate the difference between each sales area and the zone average

Scenario description

A sales company has 7 major sales zones under the national administrative divisions. Each zone has several sales areas established according to provinces. At the end of the year, a sales manager needs to quickly determine the difference between the average sales profit of each province's sales area and the overall zone average. They also need to identify which are outstanding zones and which still need improvement. For this scenario, you can use an LOD expression and conditional formatting to quickly create such a report.

In this example, use the LOD_EXCLUDE function to find the difference between each sales area and the zone average.

Procedure

1. Create a calculated field.

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

      Meaning: Removes the province dimension from the aggregation and calculates the average sales amount at the zone level. For example, this expression calculates the average sales amount for each zone, such as East China.

   2. Then, subtract the previous LOD function from AVG([price]), as shown in the expression: AVG([price]) - AVG(LOD_EXCLUDE{[province]:AVG([price])}). This calculates the difference between each sales area and the zone average.

      

2. Create a chart.

   In this example, create a cross table.

   1. Drag the Province Average Difference field created in the previous step to the Columns area. Drag the area and province fields to the Rows area.

   2. In Style -> Conditional Formatting, set conditional formatting for this field: red for values greater than 0, and green for values less than 0.

      

   3. Click Update. The system automatically updates the chart.

      

Now, you can quickly see that in the East China zone, the sales in Shanghai, Anhui, Jiangsu, and Fujian are positive, with Shanghai performing the best. The sales in Shandong, Jiangxi, and Zhejiang are below average and need improvement.

Filter condition rules

LOD_FIXED function with external filter conditions

• Scenario where the result is not affected by filter conditions

  LOD_FIXED_1 field expression: LOD_FIXED{[Region]: SUM([Order amount])}

  Filter condition: Shipping Method = "Large Truck"

  Result description: In the figure below, using the Northeast region as an example, the total order amount is 527,400. Even when an external filter, such as Shipping Method = "Large Truck", is applied, the order amount for the Northeast region remains 527,400 because it is unaffected by the filter condition.

  Conclusion: If the filter condition dimension is different from the LOD function's aggregation dimension, the final result is not affected by the filter condition.

  

• Scenario where the result is affected by filter conditions

  LOD_FIXED_2 field expression: LOD_FIXED{[Region], [Product Type], [Shipping Method]: SUM([Order amount])}

  Filter condition: Shipping Method = "Large Truck"

  Result description: In the figure below, using the Northeast region as an example, the total order amount for Northeast - Office Supplies is 150,800, which is the sum of the amounts for three shipping methods: Large Truck (35,540), Train (103,100), and Air (12,110). When the external filter is Shipping Method = "Large Truck", the order amount for Northeast - Office Supplies is 35,540, which is the order amount for Northeast - Office Supplies - Large Truck.

  Conclusion: If the filter condition dimension is the same as the LOD function's aggregation dimension, the final result is affected by the filter condition.

  

LOD function with internal filter conditions

Conclusion: Data is filtered based on the internal filter condition before the LOD expression is calculated.

• LOD_FIXED function

  • LOD_FIXED_3 field expression: LOD_FIXED{[Region], [Product Type], [Shipping Method]: SUM([Order amount]): [Order Level]='Medium'}

    Result: In the figure below, using the Northeast region as an example, the order amount for Northeast - Office Supplies - Large Truck is 35,070. This amount is the data after filtering for Order Level = 'Medium', which is the order amount for Northeast - Office Supplies - Large Truck - Medium.

    

  • LOD_FIXED_4 field expression: LOD_FIXED{[Region], [Product Type], [Shipping Method]: SUM([Order amount]): [Shipping Method]='Large Truck'}

    Result: In the figure below, using the Northeast region as an example, the order amount for Northeast - Office Supplies - Large Truck is 35,540. This means that when the internal filter condition is consistent with the LOD aggregation granularity, the LOD_FIXED function filters the data for Shipping Method = 'Large Truck'.

    

• LOD_INCLUDE function

  LOD_EXCLUDE and LOD_INCLUDE have the same logic. Here, we use LOD_INCLUDE as an example.

  

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

    Result: In the figure below, using the Northeast region as an example, the order amount for Northeast - Office Supplies - Large Truck is 35,070. This amount is the data after filtering for Order Level = 'Medium', which is the order amount for Northeast - Office Supplies - Large Truck - Medium.

  • LOD_INCLUDE_2 field expression: LOD_INCLUDE{: SUM([Order amount]): [Shipping Method]='Large Truck'}

    In the figure below, using the Northeast region as an example, the order amount for Northeast - Office Supplies - Large Truck is 35,540. This means that when the internal filter condition is consistent with the LOD aggregation granularity, the LOD_INCLUDE function filters the data for Shipping Method = 'Large Truck'.