Calculating the YOY Ratio of Monthly Accumulated Sales

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.

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.

2. Calculating the Accumulated Sales for the Same Period Last Year

Filter condition: Year=EARLIER(Year-1), Month<=EARLIER(Month).

3. Calculating the YOY Ratio

Formula: YOY ratio=(monthly accumulated sales/monthly accumulated sales last year-1)*100% .

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".