1. Overview
1.1 Expected Effect
For example, given the daily number of stocks bought and sold, it is necessary to count the cumulative number of remaining stocks each month, as shown in the following figure:
1.2 Implementation ideas
Use the ACC_SUM function.
2.Concept
| Grammar | ACC_SUM(x_agg(array),range) | According to the added fields of the horizontal and vertical axis or the row and column dimensions, the indicator is calculated for the cross-row accumulation. |
|---|---|---|
| Parameter1 | x_agg(array) | The first parameter is the indicator calculated by the user, which must be an aggregate function or aggregate indicator. |
| Parameter2 | range | The second parameter range is the calculation range set by the user, 0 is to accumulate all rows, and 1 is to accumulate all rows in the group. |
3. Examples
3.1 Calculate the monthly accumulated remaining number
Knowing the daily number of stocks bought and sold, it is necessary to count the number of remaining stocks each month.
Drag "Date" into the dimension column and set it to the "Year Month" format, and drag "Buy" and "Sell" into the indicator column, as shown in the following figure:
Add a calculation indicator and name it "Cumulative remaining stocks", enter the formula: ACC_SUM(SUM_AGG(buy-sell),0), click "OK", as shown in the figure below:
Formula description:
| Formula | Description |
|---|---|
| SUM_AGG(buy-sell) | The daily buy-sell difference is summed and summarized according to the "year month" dimensions. |
| ACC_SUM(SUM_AGG(buy-sell),0) | Take the month as the unit, accumulate all the values of the daily buying and selling differences from top to bottom. |
You can see that the cumulative value of "2016-01" equals to "-469+2562=2093", as shown in the figure below:
For the writing of functions, please refer to: Function Calculation Format.
3.2 Effect view
See section 1.2 of this article for details.
Previous：Overview of Quick Calculation Functions
