Overview
Note: Users with FineReport 11.0.4 and later versions are advised to calculate MoM (month-on-month) through the MOM function.
Application Scenario
YoY (year-on-year) is a ratio calculated by dividing the value in a month/quarter of the current year by the value in the same period of the previous year. For example, the formula to calculate YoY of the value in the Nth quarter of a year to the value in the same period of the previous year is current year's value / previous year's value * 100%.
YoY growth compares growth in a smaller period of time (e.g. a month/quarter) against a comparable period (e.g. that same month/quarter in the previous year). For example, the formula to calculate YoY growth of the value in the Nth quarter of a year to the value in the same period of the previous year is (current year's value - previous year's value) / previous year's value * 100%.
Implementation Method
Retrieve data from the same quarter of the previous year through hierarchical coordinates and calculate data through the IF() function.
Example
Data Preparation
Create a regular report and create a built-in dataset named Embedded1, as shown in the following figure.
Report Design
Design the table as shown in the following figure. Drag the fields in Embedded1 to cells A2, B2, and C2. Perform YoY for cell D2 and YoY growth for cell E2.
Enter the formula IF(&A2 > 1, C2 / C2[A2:-1], 0) for cell D2. The following table describes the formula.
| Formula | Description |
|---|---|
C2[A2:-1] | Represents the value of cell C2 corresponding to the same quarter of the previous year in cell A1 (the cell above cell A2 (year)), namely, C2[A2:-1,B2:+0]. |
&A2>1 | Represents the calculation starts from the next year of cell A2. |
IF(&A2 > 1, C2 / C2[A2:-1], 0) | Represents that parent cells (quarter cell) expand with quarters. That is, if the condition &A2>1 (starting from the next year of cell A2) is met, it returns YoY of the current quarter's value to the previous quarter's value in the previous year. If the condition is not met, it returns 0. |
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 YoY.cpt.
