In table components, you may need to calculate the year-over-year or period-over-previous period comparison values for multiple metrics. Setting each metric individually can be cumbersome. Business Intelligence (BI) offers a batch calculation capability, allowing you to calculate multiple metrics simultaneously.
Edition support: Basic Edition and Professional Edition.
Component support: Currently, batch calculations are only supported for tables and crosstabs.
Through this document, you will learn the following:
How to add batch calculations in a table/crosstab.
How to set the batch calculation format.
FAQs of batch calculations.
Enabling Batch Calculations
1. Adding Batch Calculations
1.1 Go to the table/crosstab component editing interface: Go to the console. Select Project, Webpage, Component, and then Edit sequentially.
Note:
Since only the calculations of year-over-year and period-over-previous period comparison values are currently supported, ensure the following before adding batch calculations:
The current component dimension contains a time-type field.
The aggregation granularity of the time field is day or above (such as week or month).
1.2 Click the "+Batch" icon, and a pop-up layer for page settings will appear.
1.3 Click "+ Add" to add a calculation field. You can set the following information:
Setting Item
Description
Example
Field name
This item will appear in the table column.
If you want to calculate the month-over-previous month comparison values, enter "MoM".
Field calculation
Currently, the calculations of year-over-year and period-over-previous period comparison values are supported. The available granularities of year-over-year and period-over-previous period comparisons are determined by the time dimension aggregation granularity of charts.
If the time field "data time" is aggregated by "month", select "MoM" and then "Month-over-Month".
Field display
Specifies whether the current field is added to rows or columns.
If you want to display "Month-over-Month" on the right of the "sales" metric, select "as a column".
Offset variable
When the associated data table of the component is created using SQL and the SQL uses time-type parameters, you can bind the time variables in SQL. During execution, both the current system time and comparison time will be passed to the SQL parameters.
If the SQL sets "sale_date >${st} and sale_date<${st-30d}", you can select the time variable "st" to apply an offset for year-over-year and period-over-previous period comparisons.
Calculation metric
Specifies all or part of metrics to participate in calculations. Unselected metric fields will not participate in calculations.
If you only want to calculate the month-over-previous month comparison values for "sales" and "profit", select the "sales" and "profit" metrics.
Calculation metric - Set calculation result style
Set the data result style for metric calculations
For details, see "Setting the Batch Calculation Format" below.
Exception handling
When "growth rate" is selected for calculations, you may encounter issues that the metric value of the previous period is zero or a negative number. Exception handling can help prevent data calculation errors or display data more reasonably.
If you want to display a department with zero sales in the previous period as "-" when calculating "sequential period-over-period (growth rate)", select "show as -".
Annotation
Annotations are supported for positive and negative values.
If you want to annotate negative profit values in red font, select "negative values highlighted in red".
2. Setting the Batch Calculation Format
Click the calculation metric setting icon to style the field metric calculation results.
The style settings for metric calculation results include the following content:
Setting Item
Description
Example
Display type
Options include standard numerical value, percentage, and percentage point.
Standard numerical values support unit settings.
Percentages and percentage points do not support unit settings.
The calculation of percentage points always uses the current value minus the value from the previous period, whether for "growth rate" or "growth value".
If you want to view the percentage point increase in the current "profit margin" compared to the previous period, select "percentage point (PP)".
Decimal place
You can set the number of decimal places for results.
If you want to display data with accuracy up to 0.00 percentage points, set the number of decimal places to 2.
Thousand separator
If this setting is enabled, values greater than 1000 will be displayed with thousand separators.
If you want to display the "sales" period-over-previous period value with thousand separators, enable "thousand separator".
Apply to all
When multiple metrics are present, you can use "apply to all" to quickly unify the result format.
If you want to unify the format configurations for metrics such as "attrition rate" and "churn rate" with "profit margin", click "apply to all" under the "profit margin" configurations.
FAQs
Why Are Field Calculation Settings Gray?
If field calculation settings are gray, the possible reasons are as follows:
No time dimension fields are added: Drag and drop a time-type field into the "Dimension" box (or row/column dimension box).
A time dimension field exists, but no time aggregation is set for the time dimension field: Hover the mouse over the dimension field and select time aggregation (at least select "day" or above).
A time dimension field exists, but the time aggregation is set to "hour", "minute", or "second": Hover the mouse over the dimension field and select time aggregation (at least select "day" or above).
Why Can't I Calculate "Day-Over-Day" After Setting a Time Dimension Field?
If a time dimension field exists but you cannot calculate the day-over-previous day comparison result, refer to the following to troubleshoot.
Whether the field is of "time" type: Check whether the field is displayed as shown in the figure below. If not, go to the data table to edit the field type.
Whether time aggregation is set to "day": The granularity of period-over-previous period comparisons is based on the aggregation granularity of the current time field and should be the same as the aggregation granularity of the time field. For example, if time aggregation is set to "month", you cannot perform "day-over-day" comparisons but can only perform "month-over-month" comparisons.
Why Can't I Calculate "Day-On-Day" After Setting a Time Dimension Field?
If you cannot set "day-on-day" or other year-over-year comparison granularities, check:
Whether the aggregation granularity of the current time field is correct: The granularity of year-over-year comparisons is based on the aggregation granularity of the current time field and should be greater than the aggregation granularity of the current time field. For example, if time aggregation is set to "month", you cannot perform "day-on-day comparison" or "month-on-month comparison" but only "Year-on-Year (YoY)".
Why Does "+Batch Computing" Show the Red Text in the Figure Below?
If the red text appears as shown in the figure above, it means that the current settings are invalid. In this case, check the following reasons:
Whether the time dimension field is deleted.
Whether the time aggregation granularity is altered: Changing the time aggregation granularity invalidates the previous calculation.
