Calculate the average of the previous n days/when data is not unique

1.1 Expected effect

Example 1: For example, you need to calculate the average sales of the three days before the current date, as shown in the following figure:

Example 2:

For example, you need to calculate the average number of orders for weekday x between 2020-01-01 and 2020-02-14, as shown in the following figure

The trend chart is shown below:

1.2 Implementation ideas

Example 1: Use PREVIOUS_PERIOD to calculate the data of three days before the current date, and then use the average formula: (previous day's sales + previous two days' sales + previous three days' sales)/3.

Example 2: Due to the repetition of date data, that is, there are multiple orders on the same day, it is necessary to group and summarize the number of orders on a non-repetitive date within a period of time through the self-service dataset, and how many orders within a period of time of weekday X.

Use the aggregate function on the dashboard to divide the total number of orders by the number of weekday x to calculate the average.

Sample data: Built-in DEMO "FRDemo_ORDERS".

2.1 Create a dashboard

Select "FRDemo_ORDERS" and click "Create Component", as shown in the figure below:

2.2 Calculate the sales in the previous three days

Enter the component editing interface, add a calculation indicator, name it "previous day's sales", enter the formula: PREVIOUS_PERIOD(SUM_AGG(AMOUNT)), click "OK", as shown below:

Formula description:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Calculate the "previous two days' sales" input formula: PREVIOUS_PERIOD(previous day's sales), "previous three days' sales" input formula:PREVIOUS_PERIOD(previous two day's sales), as shown in the figure below:

Note: Since the parameters in the function are already aggregate indicators, they can be calculated normally.

2.3 Calculate the average of the previous three days

Add a calculation indicator, enter the name "Average", enter the formula: (previous day's sales+previous two days' sales+previous three days' sales)/3, click "OK", as shown in the figure below:

2.4 Effect display

See section 1.1 of this article for details.

Sample data: average sample data.xlsx

Upload sample data to FineBI.

3.1 Calculate the number of weekday x and the total number of orders in a period of time

Create a self-service dataset, select all the indicators under the sample data, as shown in the following figure:

Due to the repetition of date data, that is, there are multiple orders on the same day, it is necessary to group and summarize the number of orders corresponding to unique dates through the self-service dataset; at the same time, record the number of "weeks" on different dates. It is to calculate the number of weekday x in a period of time to facilitate the subsequent calculation of the average number of orders.

Add a "Group Summary" step, as shown in the following figure:

Save and update the self-service dataset.

3.2 Calculating the average

Create a dashboard, enter the component editing interface, create a new calculation indicator, name and enter the formula: click "OK", as shown in the following figure:

Formula description:

3.3 Effect display

See section 1.1 of this article for details.