New Calculated Metrics Explanation for HENGSHI SENSE

Explanation, applicable scenarios, and use cases for new calculated metrics.

The following is a detailed explanation using coffee analysis as an example. Data and analysis scenarios for coffee analysis are explained in detail in the Advanced Calculations Usage Instruction.

Explanation

In charts, Calculated Metrics can be added based on business scenarios. Calculated Metrics are aggregate expressions added in charts based on existing fields. They are stored as a calculation expression and only make sense in specific exploration scenarios when depending on the dimensions of a chart for grouping.

Calculated Metrics differ from the original and new fields in a data set. Metrics do not convey grouping meaning by themselves and are stored as expressions that only serve as measures in charts, not as dimensions.

As shown in the following figure, on the chart editing page under Fields and Metrics, you can click + and choose Create New Calculated Metric to add a calculated metric:

Click Create New Calculated Metric, and the page will navigate to the window for adding a new calculated metric:

For example:

Create a new calculated metric Sales Volume with the corresponding expression sum({Quantity}), click save, and the new metric is successfully added.

The meaning of this metric is to summarize and sum the sales quantities. Since the metric itself does not carry group_by() information, it is necessary to provide grouping information during use. The metric will then compute different aggregate results based on different grouping information in various exploration scenarios.

Scenario One:

In a specific exploration scenario, if dimension grouping is chosen as Order Date (Year) and the metric Sales Amount is used as a measure, the orders will be grouped by the year, and the aggregate value is calculated for each year with sum({Quantity}). That is, after grouping by year, the sum of quantities serves as the metric for each year;

Scenario Two:

In a specific exploration scenario, if dimension grouping is chosen as Region and the metric Sales Amount is used as a measure, the orders will be grouped by the region, and the aggregate value for each region is calculated with sum({Quantity}). That is, after grouping by region, the sum of quantities serves as the metric for each region.

Calculated metrics expressions can contain any specifically written function expression for the business scenario, but the outermost layer of the expression must be an aggregate expression. Examples include:

max(trunc_month({Order Date}))
sum({Quantity} * {Price})
distinct_count(concat({first_name}, '—',{last_name}))

These are examples of relatively simple calculated metric expressions.

Additionally, Calculated Metrics only store their corresponding expressions, not the specific calculation results, which means they occupy less space and perform better.

Please note:

When adding a new calculated metric, you can only choose Advanced Expressions

The expression of the calculated metric must be an aggregate expression, that is, the outermost function in the expression must be an aggregate function, such as: sum(), max(), distinct_count(), etc.

Applicable Scenarios

Calculated Metrics must be aggregate expressions. When used as measures in charts, they are roughly equivalent to incorporating the calculation method into the expression at the time of adding a new metric.

When using fields from the data set as measures, an aggregation method, I.e., calculation method, must be selected:

Calculated metrics themselves are aggregate expressions, simply wrap the required functions inside the outermost layer of the expression formulating the calculation method:

As the animation above shows: when adding a new calculated metric Total Quantity, with the expression sum({Quantity}), in the chart, under the same dimension field situation, the result is consistent with directly adding the Quantity field as a measure with the calculation method Sum.
The expression of the calculated metric includes grouping functions

In some cases, window functions are needed for calculation within the expression of the calculated metric, such as:

sum(sum({Quantity})) over (partition by {Store} order by trunc_year({Order Date}))

The expression already includes the grouped fields Store, Order Date:

After adding the calculated metric, if you select any field as a dimension and use the metric as a measure, an error message will appear:

You need to include the group fields from the metric's expression into the dimensions, then it will work properly:

Use Cases

Taking coffee analysis as an example:

Scenario One:

Statistically calculate the number of in-store orders and take-out orders each month separately. The field Order Type indicates the order status for each order, where Order Type = 1 is for in-store orders and Order Type = 0 for take-out orders.

Using the original dataset field makes it difficult to meet this requirement. By using the new metrics, this requirement can be met quickly. New calculated metrics can be added:

New Metric Monthly In-store Order Count, with expression:

sum(count(if({Order Type} = 1, {Order ID}, NULL))) OVER (ORDER BY trunc_month({Order Date}) ASC)

New Metric Monthly Take-out Order Count, with expression:

sum(count(if({Order Type} = 0, {Order ID}, NULL))) OVER (ORDER BY trunc_month({Order Date}) ASC)

In the chart, select Order Date as the dimension, aggregate by month, and use the new metrics Monthly In-store Order Count and Monthly Take-out Order Count as measures, as shown in the following diagram:

Scenario Two:

Statistically calculate the coffee sales amount in different regions for different years. The field Price indicates the unit price of each type of coffee, and Quantity represents the sales quantity for each order.

You can quickly meet this requirement through creating a new calculated metric:

Sales Amount: sum({Price} * {Quantity})

In the chart, select Region as the dimension, aggregate by month, and use the new metric Sales Amount as the measure, as shown in the diagram below: