Overview
Background
The accumulated sales as of each month every year can be calculated through the quick calculation function in FineBI.
But in daily work, there is a need to compare the year-on-year (YOY) ratio of accumulated sales as of each month, which cannot be achieved through quick calculation. In this case, the DEF function can come in handy.
Preview
Calculate the YOY ratio of accumulated sales as of each month and the result is shown in the following figure.
As you can see, the accumulated sales volume as of June 2013 was USD 1.1657 million, while that as of June 2014 was USD 1.3855 million, with a year-on-year increase of 18.86%.
For online preview, click YOY Ratio of Accumulated Sales as of Each Month.
Procedures
Adding Data
Sample data: Supermarket Sales Data.xlsx
Create a subject and upload the sample data, as shown in the following figure.
Processing Data
To sum up monthly sales in ascending order for year-on-year comparison, you can extract Year and Month during data processing.
1. Click Get Time to extract Year from Time of Order.
2. Click Get Time to extract Month from Time of Order.
3. Click Save All and Update.
Creating a Component
Drag Time of Order into Dimensions twice and change the group way to Year and Month respectively, as shown in the following figure.
Calculating the Accumulated Sales as of Each Month
Add a calculation indicator to sum up the sales as of the current month each year. The filter condition is Year=EARLIER(Year), Month<=EARLIER(Month), as shown in the following figure.
Through this formula, the result of March 2014 is the accumulated sales in January, February, and March.
| Formula | Description | Function Reference |
|---|---|---|
SUM_AGG(Sales Volume) | Sum up the total sales volume. | |
DEF_ADD(SUM-AGG(Sales Volume),[],[Year=EARLIER(Year),Month<=EARLIER(Month)]) | Calculate the accumulated sales as of each month.
| / |
Calculating the Accumulated Sales as of Each Month Last Year
To calculate the accumulated sales as of each month last year, you only need to change the filter condition to Year=EARLIER(Year-1),Month<=EARLIER(Month), as shown in the following figure.
| Formula | Description | Function Reference |
|---|---|---|
DEF_ADD(SUM-AGG(Sales Volume),[],[Year=EARLIER(Year-1),Month<=EARLIER(Month)]) | Calculate the accumulated sales as of each month last year.
| / |
Calculating the YOY Increase Ratio
YOY increase ratio = accumulated sales as of each month / accumulated sales as of each month last year - 1, as shown in the following figure.
| Formula | Description | Function Reference |
|---|---|---|
Accumulated Sales as of Each Month/Accumulated Sales as of Each Month Last Year-1 | Calculate the YOY increase ratio of the accumulated sales as of each month. | / |
IF(ISNULL(Accumulated Sales as of Each Month Last Year),"",Accumulated Sales as of Each Month/Accumulated Sales as of Each Month Last Year-1) | Determine if Accumulated Sales as of Each Month Last Year is null: if so, the formula returns null; if not, the formula returns the YOY ratio. |
Creating a Group Table
Set the chart type as Group Table, and drag related fields into the analysis area. Set the group way of the first Time of Order field as Year, and the second as Month, as shown in the following figure.
You can change the Numeric Format of fields related to sales volume to Ten Thousand, and that of YOY Ratio to Percentage.
Through previous steps, the component showcasing the YOY ratio of accumulated sales as of each month is created.
Demonstration
For the demonstration of this component, see section "Preview".
Previous：Monthly Profit Days Statistics
