How to configure cumulative calculation operations - Quick BI

Cumulative calculation accumulates the results returned from the database sequentially, allowing users to visually observe the increase process of metrics. This topic describes how to configure cumulative calculation.

Prerequisites

You have created a dashboard. For more information, see Create a Dashboard.
A dataset is selected and chart fields are configured. For more information, see Configure Chart Fields.

Usage Notes

Totals and subtotals do not participate in cumulative calculation and cannot coexist with cumulative calculation. If you select cumulative calculation, totals and subtotals are automatically disabled (displayed as "-").
Empty values are not included in cumulative results and do not affect other data results. The empty values themselves remain empty (displayed as "-").
This analysis method is based on the returned result set calculation. The current limit in the public cloud is 10,000. If the total amount of returned data exceeds 10,000, the calculation results may not meet expectations.

The following charts support cumulative calculation.

Cumulative calculation method	Description	Supported chart types
Overall cumulative	Overall cumulative calculation accumulates all values of the metric, adding metric values from top to bottom.	Line/area charts: line chart, area chart, stacked area chart, 100% stacked area chart, and combination chart. Column/bar charts: column chart, stacked bar chart, 100% stacked bar chart, ring column chart, ranking board, bar chart, stacked bar chart, 100% stacked bar chart, waterfall chart, bullet chart, box plot, and histogram. Bubble/scatter charts: bubble chart, scatter chart, and facet scatter chart. Table charts: cross table, detail table, and heat map. Pie/ring charts: pie chart, rose chart, radar chart, and treemap chart. Geographic charts: color map, bubble map, heat map, flow map, and symbol map. Metric charts: metric card and metric trend chart. Funnel/conversion charts: funnel chart and comparison funnel chart. Other charts: word cloud.
Group cumulative	Group cumulative calculation accumulates all values of the metric within a group, adding metric values from top to bottom within the group.	Line/area charts: line chart, area chart, stacked area chart, 100% stacked area chart, and combination chart. Column/bar charts: column chart, stacked bar chart, 100% stacked bar chart, bar chart, stacked bar chart, 100% stacked bar chart, bullet chart, box plot, and histogram. Bubble/scatter charts (x-axis cannot be a measure): bubble chart, scatter chart, and facet scatter chart. Table charts: cross table and heat map.

Configuration Entry

In the Fields panel of the chart, find the measure field that you want to apply cumulative calculation to, click the icon on the right, and select Advanced Calculation > Cumulative Calculation from the dropdown list. Then set the cumulative type based on your business scenario.

The cumulative calculation methods available for cross tables differ from those for other charts. The specific descriptions are as follows.

Chart type	Cumulative calculation method		Limitations	Calculation logic
Cross table	Overall cumulative		/	All data is accumulated from top to bottom.
	Group cumulative		/	Groups data by the finest granularity and accumulates values from top to bottom within each group. For example, if there are three dimensions: region, province, and city, cities are grouped by their respective provinces and regions for cumulative calculation.
	Custom	Column cumulative	The Row area has dimension fields	All data is accumulated column by column from top to bottom.
		Column group cumulative	The Row area has two or more dimension fields	Groups data in a single column by the selected dimension and accumulates values from top to bottom within each group. The grouping logic is: starting from the first dimension in the Row area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping. For example, in the following figure, the region and province fields will be used for grouping.
		Row cumulative	The Column area has dimension fields	All data is accumulated row by row from left to right.
		Row group cumulative	The Column area has two or more dimension fields	Groups data in a single row by the selected dimension and accumulates values from left to right within each group. The grouping logic is: starting from the first dimension in the Column area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping. For example, in the following figure, the shipping method and product type fields will be used for grouping.
Other charts	Overall cumulative		/	All data is accumulated from top to bottom.
Other charts	Group cumulative		The Color Legend or Split area has dimension fields, and for bubble charts/scatter charts, the x-axis cannot be a measure.	Groups data by dimension fields and accumulates values within each group. The grouping logic is: When there are dimensions in Color Legend but not in Split, grouping is done by the dimensions in Color Legend. When there are dimensions in Split but not in Color Legend, grouping is done by the dimensions in Split. When both have dimensions, all dimensions from both areas are treated as a whole for grouping.

Examples

You can configure cumulative calculation for chart measures on the dashboard editing page as needed. The specific instructions are as follows.

Overall Cumulative

The following example demonstrates overall cumulative calculation in a detail table.

In the Fields tab of the chart design, configure the detail table data.
Select the required dimension fields and measure fields, and double-click or drag them to the target areas.
- In the dimension list, find Region, Province, and City, and double-click or drag them to the Row area.
- In the measure list, find Order amount, and double-click or drag it twice to the Column area.
  Note
  The two Order amount fields in the Column area will display the original data and the cumulative calculation value respectively.
  When there are two duplicate fields placed in the measure area, you will see a "Duplicate measures exist" prompt. You can ignore this and update the data after the configuration is complete.
Click the icon next to the second Order amount field, and select Advanced Calculation > Cumulative Calculation > Overall cumulative.
Click Update.
This will accumulate all the returned Order amount results sequentially from top to bottom.

Group Cumulative

The following example demonstrates group cumulative calculation in a cross table.

In the Fields tab of the chart design, configure the detail table data.
Select the required dimension fields and measure fields, and double-click or drag them to the target areas.
- In the dimension list, find Region, Province, and City, and double-click or drag them to the Row area.
- In the measure list, find Order amount, and double-click or drag it twice to the Column area.
  Note
  The two Order amount fields in the Column area will display the original data and the cumulative calculation value respectively.
  When there are two duplicate fields placed in the measure area, you will see a "Duplicate measures exist" prompt. You can ignore this and update the data after the configuration is complete.
Click the icon next to the second Order amount field, and select Advanced Calculation > Cumulative Calculation > Group cumulative.
Click Update.
This will group data by the Province dimension and accumulate Order amount values from top to bottom within each group.

Custom

Currently, only cross tables support the Custom cumulative calculation method, which includes four types: Column cumulative, Column group cumulative, Row cumulative, and Row group cumulative. The following sections describe the scenarios.

Cumulative type	Configuration description	Example
Column cumulative	This example accumulates all data column by column from top to bottom.
Column group cumulative	In this example, the first dimension field in the Row area is Region, and the grouping dimension is Province. Therefore, the Region and Province fields are grouped together, and the cumulative calculation is performed at the Province dimension level.
Row cumulative	This example accumulates all data row by row from left to right.
Row group cumulative	In this example, both the first dimension field and the grouping dimension in the Column area are Product Type. Therefore, data is grouped by the Product Type dimension for cumulative calculation.