Overview
Version
FineBI Version | Functional Change |
6.0 | / |
Background
When you view the data, you may pay particular attention to the month-over-month data for the latest month. However, the date for the latest month is often incomplete. For example, when the data is updated to December 6th, calculating the MoM rate for data in December requires a comparison with data from November 1st to November 6th.
This document introduces how to correctly calculate the MoM rate for the latest month.
Procedure
You can download the sample data: Order Information.xls.
The sample data is only updated until December 6th, so when calculating the MoM rate for December, you need to compare data in December with data before December 6th.
Data Preparation
1. Create an analysis subject and upload the sample data.
2. Calculate to obtain data for the latest date in the field Date through the function Summary Column.
3. Identify the dates that can be analyzed for MoM comparisons through the function Formula Column, as shown in the following figure.
Take the sample data as an example, if the data is updated to December 6th, then data from November 7th to November 30th cannot be used for the MoM comparison calculation, but the remaining data can be used for the calculation.
Formula | Description | Reference |
AND(Latest Date＞MONTHDELTA(Latest Date,-1), MONTH(Date)=MONTH(MONTHDELTA(Latest Date,-1))) | Filters dates that cannot participate in MoM comparison calculations. | |
IF(AND(Latest Date＞MONTHDELTA(Latest Date,-1),MONTH(Date)=MONTH(MONTHDELTA(Latest Date,-1))),0,1) | Marks the dates that cannot participate in the MoM comparison calculation as 0, and marks the dates that can participate as 1. |
Component Creation
1. Create a component, copy the field Sales Volume to obtain a new field Sales Volume1, and perform Indicator Condition on the field Sales Volume1 to obtain the dates that can participate in the MoM comparison calculation.
After Indicator Condition is performed, the sales volume data from November 7th to November 30th will not be included in the field Sales Volume1.
2. Drag the field Date into Dimension, drag Sales Volume and Sales Volume1 into Indicator, click the icon next to Date, and select Year-Month from the drop-down list, as shown in the following figure.
The following figure shows that data in Sales Volume and Sales Volume1 differs only in November. This is because Sales Volume1 only includes data up to November 7th for November.
3. Click the icon next to Sales Volume1 and choose Quick Calculation > Comparison with Same Period/Comparison with Previous Period > Previous Period from the drop-down list, as shown in the following figure.
In the table, the periodic cycle data for December 2023 refers to the sales volume for the same period in November, not the total sales for November.
4. Create a calculation field (MoM Rate) to calculate and obtain data on the MoM rate. The MoM rate formula: the current value / the value in the previous period -1, as shown in the following figure.
5. Drag the field MoM Rate into Indicator and set Chart Type to Custom Chart, as shown in the following figure.
Set the fields Sales Volume and Previous Period to Column Chart, set the field MoM Rate to Line, and set the value axis of the field MoM Rate to the right-value axis. For details about custom charts, see Custom Chart.
