I. Overview
1) Totals can indicate how sums of data change dynamically, for instance:
The total assets from January to December each year.
The total assets from the first month to the last one during a period of time, e.g., two years.
2) This article introduces how to calculate the total through hierarchical coordinates.
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL function:
SELECT Year, Month, Assets FROM AssetDebt_equityGrowthWHERE YEAR='2010' OR YEAR='2011'
2. Design the report body
1) Type headers.
2) Drag data columns in [ds1] into the cell range B3:D3.
3) Preview.
3. Calculate the total
1) Input the following formula in E3: D3+E3[C3:-1].
Formula segment | Meaning |
E3[C3:-1] | Fetch the total assets by the end of the previous month during the year. If the current month is the first recorded month of the year, then the value is 0. |
D3+E3[C3:-1] | Add up the total assets by the end of the previous month and the assets in the current month to obtain the total by the end of the current month. |
2) Input the following formula in F3: IF(&C3=1, D3+E3[B3:-1,C3:!-1],D3+E3[C3:-1]).
Formula segment | Meaning |
&C3=1 | Judge whether the current month is the first recorded month of a year. |
D3+E3[B3:-1,C3:!-1] | If true, that is, the current month is the first recorded month of a year, then add up the assets in the current month and the total assets by the end of the last month of the previous year to obtain the total assets by the first recorded month of a year . |
D3+E3[C3:-1] | If false, that is, the current month is not the first recorded month of a year, then add up the assets in the current month and the total by the end of the previous month to obtain the total assets by the end of the current month. |
4. Preview
