Cumulative Sum across periods with reset

[Diinkant]

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. 

[pallavi_96]

Hi @Diinkant 

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

[Diinkant]

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.

[pallavi_96]

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))

[Diinkant]

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

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

[pallavi_96]

Hi @Diinkant ,

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

[Diinkant]

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);

[pallavi_96]

Hi @Diinkant,

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