Overview
Note:Application Scenario
Cross-level accumulation is not achieved by calculating group accumulation, but by calculating the accumulation sum in the ascending order.
For example, as shown in the following figure, cross-level accumulation is performed for the years 2010 and 2011.
Implementation Method
Perform cross-level accumulation through the formula IF(&B2 > 1, C2 + D2[B2:-1], C2 + D2[A2:-1,B2:!-1]).
Example
Data Preparation
Create a general report and create a data query named ds1. The SQL statement is as follows:
SELECT STRFTIME('%Y', Order Date) AS Year, STRFTIME('%m', Order Date) AS Month, Amount Due
FROM Order
WHERE STRFTIME('%Y',Order Date) IN ('2010','2011')
Report Design
Design the table as shown in the following figure. Drag the fields in ds1 to cells A2, B2, and C2. Perform cross-level accumulation for cell D2.
The formula for cell D3 is IF(&B2 > 1, C2 + D2[B2:-1], C2 + D2[A2:-1,B2:!-1]). The following table describes the formula.
| Formula | Description |
|---|---|
IF(A, B, C) | Represents a conditional expression. If A is true, returns B, otherwise, returns C. |
&B2 > 1 | Represents part of the conditional expression and checks whether the current value is the first month. |
D2[B2:-1] | Represents the previous accumulation result. That is, represents the value of cell D2 corresponding to the previous cell B2 (last month). |
D2[A2:-1, B2:!-1] | Represents the last accumulation result of the previous group. That is, represents the value of cell D2 corresponding to the last cell expanded from cell B2 in the previous cell A2 (last year). |
IF(&B2 > 1, C2 + D2[B2:-1], C2 + D2[A2:-1,B2:!-1]) | Represents that if &B2 > 1, from the second month, calculates the sum of the previous cumulative result plus the current month amount; if cell B2 is the first month, calculates the sum of the last cumulative result of the previous group plus the current month amount. |
Effect Preview
Save the template and click Pagination Preview. Then the preview effect is shown in the following figure.
The template can be previewed on the APP/HTML5 terminal, as shown in the following figure.
Template Download
For details, you can click to download Cross-Level Accumulation.cpt.
