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.
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.
Sample data: Supermarket Sales Data.xlsx
Create a subject and upload the sample 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.
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.
Specified dimension: No.
Dimensions for analysis: Year and Month.
Filter condition: Year=EARLIER(Year), Month<=EARLIER(Month).
DEF_ADD
2. Calculating the Accumulated Sales for the Same Period Last Year
Filter condition: Year=EARLIER(Year-1), Month<=EARLIER(Month).
Calculate the monthly accumulated sales last year.
3. Calculating the YOY Ratio
Formula: YOY ratio=(monthly accumulated sales/monthly accumulated sales last year-1)*100% .
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.
For details, see section "Preview".
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy