Calculate the cumulative average

1.1 Problem description

When using FineBI, it is sometimes necessary to find the cumulative average by dimension, such as January 2020 for the average number of products purchased in January, February 2020 for the cumulative average number of purchases in January and February, and March 2020 for the cumulative average number of purchases in January, February, and March.

For example, find the cumulative average of the number of sales based on the date (month of the year) as follows.

1.2 Implementation idea

• The cumulative value of the number of sales by month and year.

• Calculates the current cumulative number of years and months corresponding to the year and month.

• The cumulative value of the number of sales / the current cumulative number of years and months, and calculate the cumulative average value of the corresponding years and months.

Example data: Built-in data>Group Merchandise Sales Summary

2.1 Find the cumulative sales quantity

1）Create "Dashboard > Grouped Tables", select the Group Merchandise Sales Summary table, drag the "Date" field into the dimension column, drag the "Quantity" field into the indicator column, and switch the month and year display for the "Date" field, as shown in the following figure.

2）Click Add calculation indicator, here you need to calculate the cumulative value of sales for the current month's sales and the cumulative value of sales before the current month, enter the formula: ACC_SUM(SUM_AGG(Quantity),0), as shown below.

Note: The "Quantity" field needs to be selected from the field column on the left and cannot be entered manually.

Formula Description.

The ACC_SUM function represents the calculation of the accumulation of metrics across rows based on fields added to the horizontal and vertical axes or row and column dimensions.ACC_SUM(SUM_AGG(Quantity),0), In other words, you can calculate "Quantity" by year and month, and then calculate the cumulative value.

Drag the "Cumulative Sales Quantity" into the indicator column, as shown in the figure below.

2.2 Calculate the  cumulative years-months

In order to find the average cumulative sales, so you also need to calculate the current cumulative number of years and months. Click on the add calculation indicator, and the cumulative number of years and months is calculated as follows: RANK_ANLS(cumulative sales quantity,0,"asc"), as shown in the figure below.

Formula Description.

RANK_ANLS according to the horizontal and vertical axes or row dimension added to the fields of the indicator for the calculation of cross-row ranking, in fact, in order to seek the cumulative number of years and months and borrow the sorting function to sort the cumulative sales volume to get the required data.

Drag and drop it to the indicator column of the table, as shown in the following figure.

2.3 Calculate the cumulative average the corresponding year and month

Once you have the cumulative value of the number of sales and the cumulative value of the year and month, you can divide them to find the cumulative average value of the corresponding year and month.

Click on the add calculation indicator and use the cumulative value of purchased purchased products/line directly, as shown in the figure below.

Note: Both data have to be selected from the left field column and cannot be entered manually.

Drag the field into the indicator column of the form, as shown below.

2.4 Effect view

See Section 1.1 of this document for details.