Overview
Background
You can use the quick calculation function in FineBI to calculate the accumulated sales by a given month every year if you do not use the DEF function.
In the daily work, the YOY ratio of monthly accumulated sales is often needed, but it cannot be generated by the quick calculation function. Therefore, you need to use the DEF function.
Preview
Calculate the YOY ratio of accumulated sales by a given month.
In the table, you can see that the accumulated sales by June in 2013 was 116,57 thousand, while as of June in 2014, the accumulated sales was 1,385.5 thousand, up by 18.86% year-on-year.
Procedures
Adding Data
Sample data: Supermarket Sales Data.xlsx
Create a subject and upload the sample data.
Processing Data
You want to calculate the accumulated sales by a given month, so it's necessary to extract fields Month and Year during the data processing to sort months in the ascending order.
1. Click Get time to get the year of the order date.
2. Click Get time to get the month of the order date.
3. Click Save and Update.
Creating Components
The expected effect is shown below:
1. Calculating the Monthly Accumulated Sales
Add a calculation indicator to calculate the accumulated sales by a given month. The filter condition is Year=EARLIER(Year), Month<=EARLIER(Month).
For example, the generated result of sales in March in 2014 would be the sum of sales in January, February and March.
Formula | Description | Help Document |
SUM-AGG (Sales Volume) | Calculate the profit sum | SUM-AGG |
DEF_ADD(SUM-AGG(Sales Volume),[],[Year-EARLIER(Year),Month<=EARLIER(Month)]) | Calculate the accumulated sales by a given month every year.
| DEF_ADD
|
2. Calculating the Accumulated Sales for the Same Period Last Year
Filter condition: Year=EARLIER(Year-1), Month<=EARLIER(Month).
Formula | Description | Help Document |
DEF_ADD(SUM-AGG(Sales Volume),[],[Year-EARLIER(Year),Month<=EARLIER(Month)]) | Calculate the monthly accumulated sales last year.
| DEF_ADD
|
3. Calculating the YOY Ratio
Formula: YOY ratio=(monthly accumulated sales/monthly accumulated sales last year-1)*100% .
Formula | Description | Help Document |
Monthly accumulated sales/monthly accumulated sales last year-1 | Calculate the YOY ratio | / |
IF(ISNULL(Monthly Accumulated Sales Last Month),"",Monthly Accumulated Sales/Monthly Accumulated Sales Last Year-1) | If the value of Monthly Accumulated Sales Last Year is null, no result will be displayed. Otherwise the YOY ratio will be displayed. | IF |
4. Creating Group Table
Set the chart type as Group Table, and drag fields into the corresponding columns for analysis. Set the first Time of Order field as year, and the second as Month.
You can change the number format of all those sales-related fields into Ten Thousand, and the number format of the YOY Ratio field into Percentage.
Effect Display
For details, see section "Preview".
