Analytic functions (LOD functions)

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.

Description

LOD_FIXED

LOD_INCLUDE

LOD_EXCLUDE

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

• Description

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

Filtering condition

In addition to basic formulas, 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

Description

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.

Procedure

1. create a calculated field

   Field expression: lod_fixed{[region]:BI_SUM([order amount])}

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

   

2. Create a chart.

   In this example, we create a crosstab.

   Drag the Amounts and fields created in the previous step to the Columns section, and drag the Region and Province fields to the Rows section. Then, click Update to update the chart.

   

   At this time, we can have the same sum in the same region, regardless of the province.

Scenario 2: Customer order frequency

Description

The sales management of a company wants to understand the number of customers who have ordered one order, two orders, three orders (and so on), and analyze the repurchase stickiness of customers by viewing the number and distribution of customer purchases times. In this scenario, we can use level-of-detail expressions (LOD functions) to achieve the purpose of dividing a metric by another metric.

In this example, we use the lod_fixed function to convert the number of orders into a dimension by the number of customers and find the customer order frequency.

Procedure

1. Create a calculated field.

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

   Meaning: Find the number of purchases for each user based on the user name.

2. Allows a RAM user to create a chart.

   In this example, we create a column chart.

   Drag the Number of Purchases field created in the previous step to the Category Axis /Dimension Axis section. Drag the customer_name field to the Axis Value /Measure section and set the deduplication count. Click Update to update the chart.

   

At this point, we can see that the largest number of customers have purchased 7 times, and the largest one has purchased 58 times.

Scenario 3: Profit percentage ranking by region

Description

A company's regional sales director wants to know each region's share of total profit and see at a glance which regions contribute the most. In this scenario, we can use advanced calculation-> proportion to implement, or we can use detailed level expressions (LOD functions) for more flexible implementation.

In this example, we use the lod_fixed function to find the regional profit percentage rankings.

Procedure

1. Create a calculated field.

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

      Meaning: In this example, the fixed expression is written in a more special way. The expression does not specify dimensions, so it means that the sum of profits is directly calculated without aggregation according to any granularity.

   2. Then, divide the preceding LOD function by SUM([profit_amt]), as shown in the following expression: SUM([profit_amt]) / SUM(lod_fixed{:SUM([profit_amt])}) to obtain the profit percentage of each region.

      

2. Create a chart.

   In this example, we create a leaderboard.

   Drag the Profit Percentage field created in the previous step to the Indicator /Measure area, drag the area field to the Category /Dimension area, and then click Update. The system automatically updates the chart.

   

At this point, we can see that South China and North China are the top two contributors, and the southwest has the lowest contribution, and the profit is even negative.

Scenario 4: Annual new user statistics

Description

In a product, how to judge whether our product is growing positively? In addition to PV and UV used in daily life, we can also count the loyalty rate of users. For example, when users who have used our products for a long time are still using our products and making contributions such as placing orders, we can think that our products are sticky. To achieve this effect, we can use level of detail expressions (LOD functions).

In this example, we use the lod_fixed function to implement the annual new user statistics.

Procedure

1. Create a calculated field.

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

   Meaning: Find the earliest time when each user placed an order. The granularity is year. You can adjust the granularity as long as you use a date function that conforms to the syntax of your database.

2. Create a chart.

   In this example, we create a line graph.

   Drag the Customer Earliest Purchase Year field created in the previous step to the Category Axis (Dimensions) field, drag the Sales value to the Axis Value (Measures) field, and then click Update. The system automatically updates the chart.

   

At this time, we can see that the users who started using in 2013 are still very high in the overall contribution. It seems that our products are still very sticky.

Scenario 5: Statistics on the trend of new customers by year based on order details

Description

The earliest purchase year of a single customer is calculated by customer granularity, and then the distribution of the number of customers is calculated based on it.

Procedure

1. create a calculated field

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

      Name the field Customer First Purchase Year, set the data type to Dimension, and set the field type to Text.

   2. Field 2: Number of customers =count(distinct [customer ID])

2. Allows a RAM user to create a chart.

   In this example, create a column chart to display data. Drag the Customer First Purchase Year field to the Category Axis (Dimensions) field and the Customer Number field to the Value Axis (Measures) field. Then, click Update. The system automatically updates the chart.

   

In this case, we can see the annual number of new customers in each year, with the largest number in 2021 and a significant decline thereafter.

Scenario 6: Statistics on the distribution of customers with different purchase frequencies based on order details

Description

The purchase frequency of a single customer is calculated by customer granularity, and then the distribution of the number of customers is calculated based on it.

Procedure

1. create a calculated field

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

      Name the field Customer purchase frequency, set the data type to Dimension, and set the field type to Text.

   2. Field 2: Number of customers =count(distinct [customer ID])

2. Allows a RAM user to create a chart.

   In this example, create a column chart to display data. Drag the Customer Purchase Frequency field to the Category Axis (Dimensions) section and the Customer Number field to the Value Axis (Measures) section. Then, click Update. The system automatically updates the chart.

   

At this time, we can see that the number of customers with a purchase frequency of 3 is the largest.

Scenario 7: Count the order amount of each year and compare it with the key year 2023

Description

Statistics of fixed-range data and comparison, for example, statistics of the order data of each year compared 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

   Description of field splitting:

   1. Calculate the 2023 order amount. 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 2023 order amount. Formula: sum([order amount])/[order amount in 2023]-1. The field is named Compared with 2023.

2. Allows a RAM user to create a chart.

   In this example, create a cross table. Drag the Order Date (year) field to the Rows section, and drag the Order Amount field and the Ratio to 23 Years field to the Columns section. Then, click Update. The system automatically updates the chart.

   

In this case, we can see the order amount of each order year and the comparison data with 2023.

Application scenario 8: Statistical analysis of profit and loss days for each month of each year

Description

Based on daily profit statistics, mark the profit and loss of each day, and then count the number of days according to the profit and loss mark and the year and month of the order.

Procedure

1. create a calculated field

   1. Field 1:

      case when 
      LOD_FIXED{[order date]:sum([profit])}>0 
      then 'profitable' 
      else 'loss' 
      end

      Name the field Daily Profit and Loss Marking, set the data type to Dimension, and set the field type to Text.

      Description of field splitting:

      1. Calculate the total profit based on the order date. Formula: LOD_FIXED{[order date]:sum([profit])}

      2. If the total profit is greater than 0, "profit" is returned. Otherwise, "loss" is returned. Formula: case when [total profit]>0 then 'profit' else' loss' end

   2. Field 2: Days =count(distinct [order date])

   3. Field 3: Month =BI_MONTH([order date])

2. Allows a RAM user to create a chart.

   We can use the split dimensions of the line column chart to make a small chart, based on the stacking trend or classification trend two ways to visualize, you can clearly see the comparison and trend of the data.

   In this example, we create one stacked column chart to show the profit and loss days distribution-the stacking trend; and two area chart to show the profit and loss days distribution.

   1. To create a stacked column chart, drag the month field to the category axis /dimension area, the day field to the value axis /measure area, the day field to the color legend /dimension area, and the order date (year) field to the split /dimension area. After you click Update, the system automatically updates the chart.

      

   2. Let's create two more area chart, one showing the number of days of loss and the other showing the number of days of profit.

      In the area charts, drag the Month field to the Category Axis (Dimensions) field, drag the Days field to the Value Axis (Measures) field, and drag the Order Date (year) field to the Split Axis (Dimensions) field.

      In the filter, drag the Daily Profit and Loss field and set the filter condition to show the number of days of loss to match "loss". The filter condition to show the number of days of profit to match "profit".

      

      After you click Update, the system automatically updates the chart.

      

At this point, we can visually see the comparison and trend of profit and loss days in each month of each year.

include function application

The include detail level expression calculates the specified dimension as the grouping basis. The role of include is based on the existing aggregation granularity, and then in-depth analysis.

Scenario 1: Calculate average customer sales

Description

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.

Procedure

1. create a calculated field

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

   Meaning: calculates the total order amount of each customer based on the user ID.

   

2. Allows a RAM user to create a chart.

   In this example, we create a crosstab.

   Drag the Order Amount and Customer Order Total fields to the Columns section, and drag the Product Type field to the Lines section. Set the aggregation method of Customer Order Total to Average. After you click Update, the system automatically updates the chart.

In this case, we can see the average customer sales corresponding to different product types.

Scenario 2: Average maximum transaction amount for each sales representative

Description

The sales director of a company needs to know the average of the maximum transaction amount reached by each sales representative, calculated by region, and display it on the map. In this scenario, we can use the level of detail expression (LOD expression) to make the data visually displayed at the regional level. We can also look down at the sales representative's level of detail to see which war zone has better sales data and which is worse, thus planning different goals for sales representatives in each region.

In this example, we use the lod_include function to find the individual sales representative's average maximum transaction amount.

Procedure

1. Create a calculated field.

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

   Meaning: On the basis of the original, add the name of the sales representative as the analysis granularity, and calculate the average value of the maximum sales.

   

2. Allows a RAM user to create a chart.

   In this example, we create a colored map.

   1. Drag the field Everywhere Sales Representative Average Max Sales Amount created in the previous step to the Colorscale /Measure, and drag the area field to the Geo /Dimension axis.

   2. In the Style-> section, mark the Maximum Value field in red.

      

      Click Update to create the LBS heat map.

      

At this point, we can see that the maximum sales volume is larger in East China and smaller in Northwest /Southwest China.

Scenario 3: Calculate the total profit of regions where the order amount is greater than the 500000 amount

Scenario Description

Calculate the order amount by region, and calculate the total profit of regions greater than 500000.

Procedure

1. create a calculated field

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

   Description of field splitting:

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

   2. Find out the area where the regional order amount exceeds the 500000 and calculate the profit amount. the formula: CASE WHEN [regional order amount]> 500000 then [profit amount] else 0 end

   3. Finally, sum (use SUM or set the aggregation method of the field in the chart to sum) to find the total profit of the area where the order amount is greater than the 500000.

2. Allows a RAM user to create a chart.

   In this example, a kanban is created 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 Indicator /Measure section. The field aggregation method is Sum. In the Cross Table section, drag the Area to the Row section. Drag the Order Amt, Profit Amt, and New Field to the Columns section. The field aggregation method is Sum. Click Update to update the chart.

   

   In this case, we can see that the regions where the regional order amount is greater than the 500000 are the Center,Northeast, East China, North China, and South China regions, and their total profit is 1.48M.

Scenario 4: Calculate the total profit of regions where the order amount of each product type is greater than the 100000 if the transportation mode is train.

Description

This scenario is an upgraded version of Scenario 3. On this basis, train is selected as the mode of transportation, product types are added as groups, and the total profit of regions where the order amount of each product type is train is greater than the 100000. This scenario involves the nesting of multiple functions. To facilitate understanding, we can split and use them.

Procedure

1. Create a new calculated field:

   SUM(
   CASE WHEN LOD_INCLUDE{[product type],[region]:sum(if([mode of transportation]='train', [order amount],0))}>100000
   then [amount of profit]
   else 0
   end)

   Description of field splitting:

   1. Calculate the order amount of each product type for which the transportation method is train by region. The field name is Product Type-Regional Order Amount. Formula: LOD_INCLUDE{[Product Type],[Region]:sum(if([Transportation Method]='Train', [Order Amount],0))}

   2. Find out the area where the order amount exceeds the 100000 and find the total profit. the formula is: CASE WHEN [product type-area order amount]>100000 then [profit amount] else 0 end

   3. Finally sum with SUM.

2. Allows a RAM user to create a chart.

   Create two kanban to display data. In the kanban 1, drag the newly created field to the Kanban Metrics /Measures area. In the kanban 2, drag the newly created field to the Kanban Metrics /Measures area and drag the Product Type to the Kanban Labels /Dimensions area. Click Update and the system automatically updates the chart. at this time, we can see that the total profit of the area where the order amount of the product type transportation mode is train exceeds 100000 is 3.1M, office supplies are 1.23M, furniture supplies are 445K, and technical supplies are 1.41M.

Scenario 5: Calculate the number of customers in areas where the order amount of each product type is greater than 100000 if the transportation mode is train.

Description

This scenario is similar to Scenario 4, except that the total profit is calculated instead of the number of customers.

Procedure

1. Create a new calculated field:

   COUNT(DISTINCT(
   CASE WHEN LOD_INCLUDE{[product type],[region]:sum(if([mode of transportation]='train', [order amount],0))}>100000
   then [user id]
   else null
   end))

   Description of field splitting:

   1. Calculate the order amount of each product type for which the transportation method is train by region. The field name is Product Type-Regional Order Amount. Formula: LOD_INCLUDE{[Product Type],[Region]:sum(if([Transportation Method]='Train', [Order Amount],0))}

   2. Find out the area where the order amount exceeds 100000 and find the corresponding customer. the formula: CASE WHEN [product type-area order amount]>100000 then [user id] else null end

   3. Finally, COUNT(DISTINCT()) is used to remove the count.

2. Allows a RAM user to create a chart.

   In this example, create a kanban, drag the field created in the previous step to the Kanban Metrics /Measures section, click Update, and the system automatically updates the chart. At this time, we can see that the number of customers in the area where the order amount of the product type transportation mode is train exceeds 100000 is 1026.

Scenario 6: Calculate the number of regions where the order amount is greater than the 500000

Description

This scenario is similar to Scenario 3, except that the total profit is calculated instead of the number of regions.

Procedure

1. New field calculation:

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

   Description of field splitting:

   1. The order amount is calculated by region. The field name is Regional Order Amount. Formula: LOD_INCLUDE{[Region]:sum([Order Amount])

   2. Find out the area where the area order amount exceeds 500000. formula: CASE WHEN [area order amount]>500000 then [area] else null end

   3. Finally, COUNT(DISTINCT()) is used to remove the count.

2. Allows a RAM user to create a chart.

   In this example, a kanban is created to display the data. You can also create a cross table to verify the accuracy of the data. In the kanban, drag the created field to the Kanban Metrics /Measures section. In the Cross Table, drag the Area to the Rows section and the Order Amt to the Columns section. Click Update to update the chart.

   

   In this case, we can see that the regions where the order amount is greater than 500000 are the Northeast, East China, North China, and South China regions.

Scenario 7: Calculate the average sales volume of each product type by province in 2024

Description

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

   Description of field splitting:

   1. When the order date is 2024, the order amount is calculated. The field is named 2024 order amount. Formula: IF(YEAR([order date])='2024',[order amount],0)

   2. Calculate the sales volume by product type and province. Formula: LOD_INCLUDE{[product type],[province],SUM[ 2024 order amount ]}

   3. The average value was determined by AVG.

2. Allows a RAM user to create a chart.

   In this example, a kanban is created 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 fields to the Kanban Metrics /Measures field, and drag the Product Type field to the Kanban Labels /Dimensions field. In the cross table, drag the Product Type field to the Rows field, and drag the Order Amount field to the Columns field. Then, filter the data generated in 2024. After you click Update, the system automatically updates the chart.

   

   At this time, we 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.

Scenario 8: Calculate the average number of customers in each province in 2024

Description

This scenario is similar to Scenario 7, except that the average sales volume is changed to 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)))
   }
   )

   Description of field splitting:

   1. When the order date is 2024, the number of customers is calculated. The field is named 2024 customers. 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 [number of customers in 2024]}

   3. AVG was used to calculate the average value.

2. Allows a RAM user to create a chart.

   In this example, a kanban is created to display the data. You can also create a cross table to verify the accuracy of the data. In the kanban, drag the created field to the Kanban Metrics /Measures section, and drag the Product Type field to the Kanban Labels /Dimensions section. In the cross table, drag the Product Type field to the Rows section, and drag the Customers of Each Product Type field to the Columns section. Then, filter the data generated in 2024. After you click Update, the system automatically updates the chart.

   Number of customers in each province=LOD_INCLUDE{[Product type], [Province]: COUNT(DISTINCT([User ID]))}

   

   At this point, we 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.

Scenario 9: Calculate the percentage of products that have reached the target and the profit target gap between the corresponding products in a province.

Description

Under the condition that the profit target of each province is relatively certain, further analysis is divided into how many products have been achieved and which products have not been achieved.

That is, the profit target gap of each product is calculated under the province dimension, and the number of products that achieve the target, the total number of products, and further calculate the proportion.

In this scenario, you need to create two charts and link them. You can click Product Statistics on the left to view the specific product information on the right.

Procedure

1. create a calculated field

   1. Field 1: Profit Target Gap

      LOD_INCLUDE{[product] : sum([profit]-[profit target])}.

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

   2. Field 2: Proportion of the number of products that have reached the target

      count(distinct 
      case when [product profit target gap]>0 
      then [product] 
      else null end)
      /count(distinct [product]) 

      Description of field splitting:

      1. When the profit target gap of the product is greater than 0, the number of products is calculated again, and the number of products reaching the target is calculated, with the formula: count(distinct case when [profit target gap of the product]>0 then [product] else null end)

      2. Calculate the number of all products by using the following formula: count(distinct [product])

      3. The preceding two fields are divided by [Number of products reached ]/[ Number of all products] to obtain the Percentage of products reached.

2. Allows a RAM user to create a chart.

   In this example, we need to create two charts and link them.

   1. Let's start by creating a bar chart to show the product's profit target gap. Drag the Product field to the Category Axis /Dimension field, and drag the Product Profit Target Gap field to the Value Axis /Measure field. After you click Update, the system automatically updates the chart.

      

   2. We create another bar chart to show the percentage of products that have reached the target. Drag the province field to the category axis /dimension field, and drag the percentage of products reached field to the value axis /measure field. After you click Update, the system automatically updates the chart.

      

   3. Set the linkage between two bar charts.

      Note

      In this example, the two bar charts use the same dataset. If automatic linkage is enabled for the dashboard, the charts are automatically associated with each other without manual configuration. If automatic linkage is not enabled, you can configure manual linkage.

      For more information, see Linkage.

      The linkage effect is displayed.

At this point, we can see the completion of the provincial profit target and view the specific products that each province corresponds to and does not achieve the target.

Scenario 10: Evaluate and compare the operation of stores in different regions

Description

According to the detailed data of store sales and gross profit, the total sales and gross profit are calculated under the regions and communities operating in each region, and the average sales and gross profit of a single store in the corresponding region are also calculated. You can also view the performance of stores at the region and community level or at the region level through filtering dimensions, and evaluate and compare the operating conditions of stores in each region.

Procedure

1. create a calculated field

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

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

2. Allows a RAM user to create a chart.

   In this example, create a cross table and configure Conditional Formatting.

   1. In the cross table, drag Region and Region to the Rows section. In the Columns section, drag two fields to Store Gross Profit. One field is set to Average and the other field is set to Sum. Drag two fields to Store Sales. One field is set to Average and the other field is set to Sum.

      

   2. The store gross profit (average) and store sales (average) fields are displayed as store average, and the store gross profit (sum) and store sales (sum) fields are displayed as total.

   3. In the Style-> Cell-> Indicator Group section, set Indicator Group.

      

   4. You can configure Conditional Formatting to make the display more beautiful. In this example, the conditional formatting of the four columns is as follows:

   5. After you click Update, the system automatically updates the chart.

At this point, we can intuitively see the corresponding sales, gross profit and store average data of each region.

Scenario 11: Evaluate regions based on their performance

Description

In each region, the sales volume is calculated based on the province summary, the number of provinces in which the sales volume is greater than the threshold (based on the input of filtering conditions), the corresponding number of customers in these provinces, and the average number of customers in each province. You can enter different thresholds to view the data results.

Procedure

1. create a calculated field

   1. Field 1: Number of provinces that meet the condition

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

      Description of field splitting:

      1. Calculate the order amount by province. Formula: lod_include{[province]:sum([order amount]). The field name is order amount by province.

      2. If the province order amount is greater than the value placeholder ord_amt_leve l, the province is returned. Otherwise, it is empty. Formula: case when [province order amount ]> $val{ord_amt_level} then [province] else null end. The field is named province that meets the condition.

      3. The number of provinces that meet the condition. Formula: count(distinct [provinces that meet the condition ])

   2. Field 2: Total number of customers in the province

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

      Description of field splitting:

      1. Calculate the order amount by province. Formula: lod_include{[province]:sum([order amount]). The field name is order amount by province.

      2. If the province order amount is greater than the value placeholder ord_amt_level, the customer ID is returned. Otherwise, it is empty. Formula: case when [province order amount ]> $val{ord_amt_level} then [customer ID] else null end. The field name is customer under province.

      3. The number of customers in the province. Formula: count(distinct [Customers in the province ])

   3. Field 3: Average number of customers in each province= [Total number of customers in each province] /[Number of qualified provinces ].

2. Allows a RAM user to create a chart.

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

   2. Insert the query criteria in the chart, set the query criteria to the sales amount of the province, the associated value placeholder ord_amt_level, and set the default value to 100000.

   3. Click Update to create the LBS heat map.

      

In this case, you can enter the sales amount of different provinces to filter and view the data changes.

exclude function application

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

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

Description

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.

Procedure

1. create a calculated field

   Field expression: lod_exclude{[province]:SUM([order amount])}

   Meaning: calculates the sales of the region after the current province is removed.

   

2. Allows a RAM user to create a chart.

   In this example, we create a crosstab. Drag the Order Amt and Region Total Sales fields to the Columns section, and drag the Region and Province fields to the Rows section. Then, click Update to update the chart.

   

In this case, we can see both the order amount of each province and the total sales of the corresponding region.

Scenario 2: The difference between the average value of each sales region and the war zone

Description

At present, a sales company has seven major war zones under its jurisdiction according to the administrative regions of the country, and several sales zones have been set up under each war zone according to the provinces. Now, at the end of the year, we need to quickly know the gap between the average sales profit of the restricted areas in each province this year and the overall average of the war zones, and see which are excellent war zones and which still need to be improved. For this scenario, we can use level of detail expressions (LOD expressions) and conditional formatting to quickly complete such a report.

In this example, we use the lod_exclude function to find the difference between each sales region and the war zone average.

Procedure

1. Create a calculated field.

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

      Meaning: On the original basis, the sales war zone is removed as the aggregation granularity, and the average sales of other aggregation granularities is calculated. In this expression, we will find the average of sales in each war zone (East China, etc.).

   2. Then use AVG([price]) to subtract the previous LOD function, as shown in the expression: AVG([price]) - AVG(lod_exclude{[province]:AVG([price])}) to obtain the difference between the average value of each sales region and the war zone.

      

2. Allows a RAM user to create a chart.

   In this example, we create a crosstab.

   1. Drag the Province Average Variance field created in the previous step to the column. Drag the area and province fields to the row.

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

      

   3. Click Update to update the chart.

      

At this point, we can quickly see that in the East China war zone, sales in Anhui, Fujian and Jiangxi are positive.

Filter Rule Description

LOD_FIXED function + external filter condition

• Scenarios in which computing results are not affected by filter conditions

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

  Filter conditions: mode of transport="kcal"

  Calculation result: As shown in the following figure, the total order amount in Center China is 3.29M. If no external filter condition is specified or the external filter condition is set to "kcal", the order amount in Northeast China is always 3.29M, which is not affected by the filter condition.

  Conclusion: The filter condition and the aggregation dimension of the LOD function are different. The final calculation result is not affected by the filter condition.

  

• Scenarios where computing results are affected by filter conditions

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

  Filter conditions: mode of transport="Truck"

  Calculation result description: as shown in the following figure, taking the Center region as an example, the total order amount of office supplies in the Center region is 1.02M, which is the total amount of the three modes of transportation: truck (71.8K), train (766K) and plain (180K). When the external filter condition is set to "Truck", the order amount of office supplies in the Center region is 71.8K, which is the order amount of office supplies in the Center region.

  Conclusion: The filter condition and the aggregation dimension of the LOD function are the same. Due to secondary aggregation, the final calculation result is affected by the filter condition.

  

LOD function + internal filter condition

Conclusion: If the internal filter conditions are the same as the aggregation granularity of the LOD, the related data will be filtered out simultaneously. Otherwise, only the LOD fields will be filtered out.

• LOD_FIXED function

  • LOD_FIXED_3 field expression: lod_fixed{[region], [product type], [shipping method]: SUM([order amount]): [order level] ='medium'}

    Calculation result: In the following figure, the order amount of Furniture Supplies-Train in Center China is 310K. This amount is the order amount of Furniture Supplies-Train-L2 in Center China.

    

  • LOD_FIXED_4 field expression: lod_fixed{[region], [product type], [shipping method]: SUM([order amount]): [shipping method] ='Track'}

    Calculation result: In the following figure, the order amount of furniture supplies in the Center China region is 685K. If the internal filter condition is the same as the LOD aggregation granularity, the lod_fixed function filters the data of the Track.

    

• LOD_INCLUDE function

  LOD_EXCLUDE and LOD_INCLUDE have the same logic. In this example, LOD_INCLUDE is used.

  

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

    Calculation result: In the following figure, the order amount of Office Supplies-Truck in Center China is 128K. This amount is the order amount of Office Supplies-Truck-L2 in Center China.

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

    In the following figure, the order amount of office supplies in the Center China region is 71.8K. If the internal filter condition is the same as the LOD aggregation granularity, the lod_include function filters the data of the transportation mode of the Truck.