I. Overview
1) Year-over-year (YoY) and month-over-month (MoM) growth rates are common indicators in a financial report:
The YoY growth rate reflects the growth in this month over the previous month;
The MoM growth rate reflects the growth in this month over the same month last year.
2) This article introduces how to calculate the YoY or MoM growth rate through hierarchical coordinates.
II. Steps
1. Definition of MoM and YoY growth rates
In different business scenarios, MoM and YoY growth rates are calculated differently. Here, they are calculated in the following way:
1) MoM growth rate: the growth rate in this month over the last month, and the two months are in the same year.
2) YoY growth rate: the growth rate in a month over the same month last year.
2. Insert the dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL statement to inquire the financial data in 2010 and 2011.
3. Design a report body
1) Drag [Year], [Month] and [Assets] in [ds1] into B3~D3.
2) Input headers in B2~D2.
3) Preview the effect.
4. Calculate the MoM growth rate
1) Input the following formula in E3:
IF(&C3 >1, IF( D3[C3:-1] ==0.0,"Denominator is zero", D3 / D3[C3:-1]-1),"The first month")
Snippet of formula | Meaning |
|---|---|
&C3 > 1 | &C3 is the serial number of a month in this year. If the number is 1, which means the first month in this year, then the MoM growth rate will not be calculated as there is no previous month. Therefore, “The first month” is displayed; If the number is greater than 1, then the following IF statement will be executed to calculate the MoM growth rate. |
D3[C3:-1] == 0.0 | D3[C3:-1] means Assets in the previous month, which is the denominator in the calculation of the MoM growth rate. If the value of Assets in the previous month is 0, this will result in an infinitely great result, so the MoM growth rate will not be calculated and "Denominator is zero" will be displayed; If the value of Assets in the previous month is not 0, then D3 / D3[C3:-1]-1 will be executed to calculate the MoM growth rate. |
2) Set the format of text in E3 to [Percentage] in the form of #0.00%, i.e., to reserve two decimal places.
3) Preview the effect:
There is no MoM growth rate corresponding to every January, so "The first month" will be displayed.
MoM growth rates are displayed in the form of percentage from every February.
5. Calculate the YoY growth rate
1) Input the following formula in F3:
IF(&B3>1,IF(D3[!0]{B3=$B3-1 && C3=$C3} == 0.0,"NA",D3/D3[!0]{B3=$B3-1 && C3=$C3}-1),"The first year")
Snippet of formula | Meaning |
|---|---|
&B3>1 | &B3 is the serial number of a year. If the number is 1, which means the first year, then the YoY growth rate will not be calculated as there is no previous year. Therefore, "The first year" will be displayed; If the number is greater than 1, then the following IF statement will be executed to calculate the YoY growth rate. |
D3[!0]{B3=$B3-1 && C3=$C3} == 0.0 | D3[!0]{B3=$B3-1 && C3=$C3} calculates the Assets in the same month last year, which is the denominator. If the value of such Assets is 0, this will result in an infinitely great result, so the YoY growth rate will not be calculated and “NA” will be displayed; If the value of such Assets is not 0, then D3/D3[!0]{B3=$B3-1 && C3=$C3}-1 will be executed to calculate the YoY growth rate. |
2) Set the format of text in F3 to [Percentage] in the form of #0.00%, i.e., to reserve two decimal places.
3) Preview the effect:
There is no YoY growth rate corresponding to any record in the first year, so "The first year" will be displayed
YoY growth rates are displayed in the form of percentage from the second year on
Previous：YoY
