Calculate the annual year-on-year cumulative amount

1.1 Expected effect

1.2 Implementation ideas

Use the quick calculation functions ACC_SUM-cumulative and SAME_PERIOD-same period to calculate the accumulated amount of the current year and the accumulated amount of the same period last year;

Since it is necessary to aggregate the cumulative amount annually and calculate the cumulative monthly amount in the same year at the same time, it is necessary to drag in the "SIGNDATE" grouped by "Year" and "SIGNDATE" grouped by "Year Month" at the same time.

Sample data: "SalesDEMO>FRDemo_ORDERS".

2.1 Create component

Select "FRDemo_ORDERS" under "Data Preparation", and create a component, as shown in the figure below:

2.2 Calculate the monthly cumulative amount of the current year

Click "+" to create the calculation indicator, name it and enter the formula: ACC_SUM(SUM_AGG(AMOUNT),1), click "OK", as shown in the figure below:

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.

Formula description:

2.3 Calculate the monthly cumulative amount of the same period last year

Click "+" to create the calculation field, name it and enter the formula: ACC_SUM(SAME_PERIOD(SUM_AGG(AMOUNT),"Y"),1), click "OK", as shown below:

Formula description:

2.4 Year-on-year calculation

Add a calculation field, name and enter the formula: cumulative amount of the current year/ cumulative amount of last year, click "OK", as shown in the figure below:

2.5 Create a group table

Drag the "SIGNDATE" field twice into the dimension column, and then set the grouping basis to "Year" and "Year Month" respectively, as shown in the following figure:

Note: The reason for using the "Year" and "SIGNDATE" fields is that when calculating the current year's cumulative amount and last year's cumulative amount, if the calculation is based only on the "year month" basis, the cumulative amount will continue to accumulate data for all years. The result is not to calculate the cumulative value in different years.

Drag three indicators into the indicator column and select "Group Table", as shown in the figure below:

At the same time, the unnecessary time data can be filtered, as shown in the following figure:

2.6 Effect view

2.6.1 PC

See section 1.1 of this article for details.

2.6.2 Mobile