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.
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.
Sample data: Supermarket Sales Data.xlsx
Create a subject and upload the sample data, as shown in the following figure.
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.
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.
SUM_AGG(Sales Volume)
Sum up the total sales volume.
SUM_AGG
DEF_ADD(SUM-AGG(Sales Volume),[],[Year=EARLIER(Year),Month<=EARLIER(Month)])
Calculate the accumulated sales as of each month.
Specified dimension: /.
Analysis area dimensions: Time of Order (group way: Year) and Time of Order (group way: Month).
Filter condition: Year=EARLIER(Year), Month<=EARLIER(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.
DEF_ADD(SUM-AGG(Sales Volume),[],[Year=EARLIER(Year-1),Month<=EARLIER(Month)])
Calculate the accumulated sales as of each month last year.
Filter condition: Year=EARLIER(Year-1), Month<=EARLIER(Month).
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.
Accumulated Sales as of Each Month/Accumulated Sales as of Each Month Last Year-1
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.
IF
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.
For the demonstration of this component, see section "Preview".
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy