topic Re: Cumulative Sum across periods with reset in App Development

Cumulative Sum across periods with reset

Diinkant — Thu, 26 Dec 2024 22:52:19 GMT

Hi,

I am trying to create a cumulative sum across various periods within a financial year but running into a few issues. Image of my data set below.

In the above image you can see the activity is the same as the cumulative sum, but that shouldn't be the case.

The formula I have tried
- RangeSum(
Above(
Sum({
<PeriodID = {">=$(vLastYearStartPeriod)<=$(vLastYearEndPeriod)"}>
} [Transaction Amount]),
0,
RowNo()
)
)

Keep in mind that another dimension is called account code. So, when it goes into a new account code, the cumulative sum should reset to whatever the activity number is.

Re: Cumulative Sum across periods with reset

pallavi_96 — Fri, 27 Dec 2024 05:17:15 GMT

Hi @Diinkant

Could you please share the dataset that includes the AccountCode for further clarification?

Re: Cumulative Sum across periods with reset

Diinkant — Sun, 29 Dec 2024 19:53:22 GMT

Hi @pallavi_96

The accountcode would like this (with the ideal outcome):

AccountCode | PeriodID | Activity | Cumulative | FiscalYear

1110.000 | 2701| 10 | 10 | 2022-2023

1110.000 | 2702 | 20 | 30 | 2022-2023

1120.000 | 2701 | 10 | 10 | 2022-2023

1120.000 | 2702 | 30 | 40 | 2022-2023

1110.000 | 2801 | 20 | 20 | 2023-2024

The cumulative sum should be a sum for that account code across that fiscal year but break down per period.

Re: Cumulative Sum across periods with reset

pallavi_96 — Mon, 30 Dec 2024 05:21:56 GMT

HI @Diinkant

Are you expecting the output below for your query?

If yes, please use the following expression

sum(Aggr(RangeSum(Above(sum(Activity), 0, RowNo())), AccountCode,PeriodID))

Re: Cumulative Sum across periods with reset

Diinkant — Mon, 30 Dec 2024 20:53:15 GMT

Thanks for that recommendation but that doesn't work either.

For Example, here are the results with the last column being the formula.

Re: Cumulative Sum across periods with reset

pallavi_96 — Thu, 02 Jan 2025 05:04:56 GMT

Hi @Diinkant ,

I used the same formula on your dataset, and it gave me the correct cumulative values for account code 1210.

Re: Cumulative Sum across periods with reset

Diinkant — Thu, 02 Jan 2025 19:52:10 GMT

Hi @pallavi_96 ,

Thanks, I have tried it with this dataset
Test:
Load * Inline [
Transaction Date,Account Code,FiscalYear,PeriodID,Transaction Amount
9-10-2022,1120.000,2022-2023,2701,100
10-10-2022,1120.000,2022-2023,2701,150
11-10-2022,1120.000,2022-2023,2701,200
9-11-2022,1120.000,2022-2023,2702,200
10-11-2022,1120.000,2022-2023,2702,300
11-11-2022,1120.000,2022-2023,2702,400
9-12-2022,1120.000,2022-2023,2703,300
10-12-2023,1120.000,2023-2024,2801,250
11-12-2023,1120.000,2023-2024,2802,350
1-10-2022,1121.000,2022-2023,2701,150
2-10-2022,1121.000,2022-2023,2701,250
3-10-2022,1121.000,2022-2023,2701,350
1-11-2022,1121.000,2022-2023,2702,150
1-11-2022,1121.000,2022-2023,2702,250
2-12-2022,1121.000,2022-2023,2702,50
9-12-2023,1121.000,2023-2024,2801,100
];

It works.

So, there must be something with the way I am loading my actual data as it works with test data.

Do I need to order my data in a specific way for it to work within the load script like I have with the test data. This is my load script:

Transactions:
[Account Code],
[Branch Code],
[Department Code],
PeriodID,
[Transaction Amount],
Month(Date(Date#([Transaction Date],'YYYY-MM-DD'),'DD/MM/YYYY')) as Month,
MonthName(Date(Date#([Transaction Date],'YYYY-MM-DD'),'DD/MM/YYYY')) as MonthName,
Year(Date(Date#([Transaction Date],'YYYY-MM-DD'),'DD/MM/YYYY')) as Year,
YearName(Date(Date#([Transaction Date],'YYYY-MM-DD'),'DD/MM/YYYY'), 0, 7) as FiscalYear,
[Transaction Date]

From [Transactions.qvd] (qvd);

Re: Cumulative Sum across periods with reset

pallavi_96 — Fri, 03 Jan 2025 05:11:10 GMT

Hi @Diinkant,

Please try to sort the 'Account Code' column in the table.