Overview
Application Scenario
Level-by-level average is obtained by adding up the data from the first level to the current level in each group of group reports.
For example, as shown in the figure below, the cumulative average values for each month in the first half of 2010 and 2011 are respectively calculated.
Implementation Method
You can refer to the implementation method of the level-by-level accumulation.
Example
Data Preparation
Create a general report and create a dataset 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) >= '2010'
Report Design
Design the table as shown in the following figure. Drag the fields in ds1 to cells A3, B3, and C3. Calculate the average for cell E3.
Enter the formula (D3[B3:-1]*(&B3-1)+C3)/&B3 for cell E3. The following table describes the formula.
| Formula | Description |
|---|---|
D3[B3:-1] | Represents the value of D3 in the previous cell (last month) expanded from cell B3, namely, the level-by-level average of the previous month. |
&B3-1 | Represents the previous cell corresponding to each value expanded from cell B3. |
D3[B3:-1]*(&B3-1)+C3 | Represents the cumulative total value up to the present within the current year. |
&B3 | Represents the cell corresponding to each value expanded from cell B3. |
(D3[B3:-1]*(&B3-1)+C3)/&B3 | Represents the cumulative average for each month in the current year. |
Effect Preview
Save the template and click Pagination Preview. The effect is shown in section "Application Scenario."
The template can be previewed on APP and HTML5 terminals.
Template Download
For details, you can click to download Level-by-Level Average.cpt.
Previous：Cross-Level Average
