Quick BI:LOD function

Use scenarios

Level-of-detail expression. Level-of-detail indicates the level of data aggregation granularity. Different levels represent different levels of aggregation and granularity of data. This allows you to specify multiple levels of data detail in a visualization.

If you need to add a dimension whose detail level is higher or lower than the detail level of the existing view, but you do not want to change the content of the existing graph, you can use the detail level expression feature.

Procedure

1. On the dataset edit page, click Create Calculated Field. The Configure dialog box appears.

   

2. Enter a field name (①), select the LOD function and field (②) from the Field Expression field, and configure configuration items (③) such as the field type.

   

3. Click OK. When you create a dashboard chart by using Add Field, you can view the total order amount in the same region, regardless of the product type.

   

Expression description

Basic Formula

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

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

  • fixed |include|exclude: delimited keyword, formulated the scope of the LOD.

  • <Dimension declaration>: specifies one or more dimensions to which you want to connect the aggregate expression. Separate each dimension with a comma.

  • <Aggregate Expression>: The aggregate expression is a calculation that defines the target dimension.

Filter

In addition to the basic formula, you can also write filter conditions in the Quick BI. The specific expression is as follows. Separate dimension declarations, aggregate expressions, and filter conditions with colons.

• lod_fixed {Dimension 1, Dimension 2...: Aggregate Expression: Filter Condition}

• lod_include {dimension 1, dimension 2...: aggregate expression: filter condition}

• lod_exclude {dimension 1, dimension 2...: aggregate expression: filter condition}

fixed function application

The fixed detail level expression is calculated by using the specified dimension and does not reference any other dimension.

Scenario 1: Calculate the total sales amount of each region

When you analyze the distribution of sales orders by region, the data table contains the area and province dimensions. You must use the fixed expression to calculate the sum of the sales amount corresponding to the area. The fixed expression does not consider other dimension levels and only calculates the dimensions referenced in the expression. Therefore, you can calculate the sum of the sales amount corresponding to the area.

Field expression: Amount of each region and= lod_fixed{[region]:SUM([order amount])}.

Scenario 2: Analyze customer re-purchases

In the marketing process, you may often need to analyze the customer purchase frequency. You can view the number and distribution of customer purchase times to analyze the re-purchase stickiness of customers. That is, you need to calculate the number of customers who have purchased once, twice, three times....

Field expression: Purchase frequency= lod_fixed{[user ID]:count(distinct([order ID])}.

include function application

The include detail level expression calculates the specified dimension as the grouping basis.

Application Scenario: Calculate Average Customer Sales

When you analyze the sales of various products, you need to view the average customer sales. You can use include to calculate the total order sales amount of each customer first, and then use the average aggregation method to calculate the display.

Field expression: Total customer order amount= lod_include{[user ID]:AVG([order amount])}.

exclude function application

The exclude detail level expression is calculated after the specified dimension is excluded.

Scenario: Calculate the sales percentage of each province in a region

When you analyze the sales data of provinces in a region, you also need to view the total sales data of the region and the sales percentage of the provinces and the provinces. In this case, you can use the exclude function to calculate the sales of the region after excluding the current province. then, the total amount of the region is calculated by using the sum method.

Field expression: Total sales by region= lod_exclude{[province]:AVG([order amount])}.