This article introduces how to use LOD expressions.

LOD stands for Level of Detail and refers to the level of detail expression functionality. Level of detail refers to the hierarchy of data aggregation granularity; different levels represent different degrees of aggregation and granularity of the data.
LOD expressions can address the issue of having multiple data detail levels within a single visualization view. To add a dimension during analysis whose level of detail is higher or lower than that of the existing visualization view, without changing the current chart display, use the level of detail expression functionality.
How to reference fields within LOD expressions
The product supports the following two field reference formats, and users can choose as needed. For more information, see Data source fields and dataset fields.

• For data source fields, use 'field name', as shown below:

• For dataset fields, use [field name], as shown below:

Because LOD function queries have a significant impact on performance, a single chart can query up to 5 fields containing LOD functions at the same time. If the number of LOD function fields in the chart exceeds 5, the chart cannot be saved.

1. Click Settings to the right of the dataset name, then select "Add field".

2. Enter the display name for the field, specify the dimension or metric category, edit the expression, and then click Save.

3. When you use the newly added field for visualization queries, you will see that the total original price for the same province remains the same, regardless of changes in order type.

LOD expressions have the following structure:
{[FIXED | INCLUDE | EXCLUDE] < dimension declaration > : < aggregation expression >}

• The fixed function supports dimension filtering and adds a filter parameter: {filter=true:fixed <dimension declaration> : <aggregation expression>}
  • filter=true: Optional parameter. If this parameter is added, the dimension filter conditions (such as region, color, and so on) and metric filter conditions (such as sales amount > 1000 and so on) set in the filter will be applied to the aggregation calculation process of the fixed function.
  • If filter=true is not added, the calculation of the fixed function will ignore all filters and be performed directly on all raw data.
• < Dimension declaration >: Specifies one or more dimensions to which the aggregation expression will be joined. Separate each dimension with a comma. For example: [Segment], [Category], [Region].
• < Aggregation expression >: An aggregation expression is the calculation performed, resulting in an aggregated value.

LOD functions as measures #

Fixed as a dimension #

FIXED #

The FIXED level of detail expression calculates values using the specified dimensions, without referencing the dimensions in the view.

Example 1: Calculate the total profit for each province #

{fixed sale_province:sum(**profit**)}
The dimensions are **sale_province** and **sale_city**, but since the FIXED level of detail expression does not consider the level of detail in the view, only the dimensions referenced in the calculation are used, which in this case is **sale_province**. For this reason, you may see that the value for a single city within each province is the same.

Example 2: Fixed as a dimension, customer purchase frequency #

Analyses of customer purchase frequency are common, where customer loyalty or repeat purchase rate is analyzed by examining the number and distribution of customer purchases.
Proposition: The distribution of customer purchase frequency, that is, how many customers have purchased once, twice, three times, and so on.

• Visualization focus: The number of customers for each purchase frequency.
• Visualization dimension: Purchase frequency (1, 2, 3, N)
• Required background information: The number of purchases for each customer
• {fixed customer id:uniq(order id)}

In the view, we use purchase frequency and customer count. This is using one value to differentiate another value. LOD fixed can easily convert a measure into a dimension, which cannot be done with include or exclude.

Example 3: Table-scoped level of detail expression #

A "table-scoped level of detail expression" simply omits the dimension qualifier; in fact, it is a fixed level of detail expression without a dimension declaration. Therefore, we can regard a "table-scoped level of detail expression" as the simplest level of detail expression.

• {avg([quantity])} Returns the average value over the entire range of quantity in the detailed data
• {sum([quantity])} Returns the sum over the entire range of quantity in the detailed data

INCLUDE #

The INCLUDE function can further subdivide aggregate functions, enabling more precise calculations. It contains two parameters, and the expression is {INCLUDE [Dimension a]:aggregate function([Metric b])}, which means aggregating metric b according to dimension a.
For example, the expression {INCLUDE [Customer id]:avg([Sales amount])} means calculating the average sales amount based on customer id. In application scenarios, when [Region] is used as a dimension, the following three metrics in the diagram have the following meanings:

• Sum(Sales amount): The total sales amount for each region
• Average(Sales amount): The average sales amount for each region (total sales amount divided by total number of orders)
• Average({INCLUDE [Customer id]:avg([Sales amount])}): The average customer sales amount for each region.

During the calculation process, first calculate the average sales amount for each customer (that customer's total sales amount divided by the number of orders for that customer), then calculate the arithmetic mean of the average sales amounts for all customers in that region (sum of average sales amounts divided by the number of customers).

Taking Northeast as an example, first calculate the average sales amount for each customer id in Northeast, then calculate the average of all customers' average sales amounts in Northeast, which is 1547.5217.

EXCLUDE #

The EXCLUDE level of detail expression specifies the dimensions to be ignored from the view's level of detail.
The EXCLUDE level of detail expression is very useful for scenarios such as "percentage of total" or "difference from overall average".
The following level of detail expression excludes [Province/Autonomous Region] from the sum calculation of [Sales amount]: {exclude [Province/Autonomous Region]:sum([Sales amount])}

{exclude [Province/Autonomous Region]:sum([Sales amount])} is the total sales amount for the corresponding region.

Case 1: Calculating the sum of maximum values #

Example scenario #

When managing data center quotas, group by psm. Obtain the maximum capacity value for each data center under each psm over the past 7 days. Then, group by psm to calculate the sum of these maximum values. At the same time, display the total sum of maximum values to evaluate the total quota capacity for each psm and data center.
For scenarios that require secondary aggregation, the LOD function can be used to handle such cases.

Design approach #

In this case, daily quota data for each psm and each data center is already available. However, resource usage fluctuates daily, so to be on the safe side, the actual quota allocated should be greater than the maximum resource quota in the past 7 days (there are multiple data centers under each psm).
Therefore, in this case, the aggregation approach is as follows:

1. Filter the data from the past 7 days, aggregate by psm and data center, and calculate the maximum quota at the data center granularity within 7 days.
2. Aggregate by psm to calculate the quota for each data center, that is, sum the maximum values mentioned above.
3. Use the total function to calculate the overall quota.

Operation steps #

1. Calculate the maximum quota at the data center granularity within 7 days
   With the fields for data center, psm, and daily quota already available, use the LOD function to set the date filter to the most recent 7 days with data, calculate the maximum quota at the data center granularity within 7 days, and save it as a metric field.
   The expression is as follows: {fixed [机房-演示],[psm-演示]:max([cpu配额])}

   tip

   Aggregate by data center and psm fields, and calculate the maximum value of the cpu quota field. Since the filter selects the last 7 days, the result is the maximum value for the last 7 days.

2. Set as metric
   Select psm as the dimension, drag the above field to the metrics area, select "Sum" as the aggregation method, and calculate the allocated quota for each psm.
3. Calculate the total quota
   Use the Total function to sum the quotas for each psm and display the result at the top of the chart.

Case 2: Specify aggregation dimension and view value distribution #

Scenario example #

Background: As shown in the figure, this is a dataset at the order granularity, containing detailed data for each order, including order ID, the province where the order occurred, order amount, and more.
Requirement: Aggregate by province to calculate the total sales amount for each province (the sum of payment amounts), and view the distribution of these sales amounts across provinces. Distribution: View the distribution within the following ranges: 0–20w, 20–40w, 40–60w, 60–80w, 80–100w, and above 100w; that is, the number of provinces whose sales amount falls within each range.

Design approach #

The challenge in this case is that the dataset is at the order granularity, while the required distribution data is at the province granularity. Therefore, it is necessary to aggregate by province first, and then perform secondary processing to view the distribution data.
The aggregation approach is as follows:

1. Specify province as the dimension, sum the payment amounts, and create the "payment amount by province" field.
2. Use the grouping function to segment the payment amounts for each province: 0(20w, 20)40w, 40(60w, 60)80w, 80~100w, 100w
3. Use the segmented payment amount field for each province as a dimension, and calculate the distinct count of provinces, that is, the number of provinces

Procedure #

1. Create a new "Payment Amount by Province" field
   Specify the [Province] dimension, calculate the sum of [Payment Amount], and save it as a dimension.
   The expression is: {fixed [Province]: sum([Payment Amount])}
2. Segment the payment amounts for each province
   Select the field and Configure Field Grouping
   Group by step size, and set the name and condition for each group as needed
3. Calculate the number of provinces in each segment
   Use the above grouping field as a dimension, drag the "Province" field to the metrics bar, and select "Distinct Count" as the aggregation method for the metric.
   Select "Bar Chart" as the chart type and set the sorting order.