Cumulative sum for all prior years per period - Rolling sum

[sush]

Hi,

Could you please let me know how can we calculate the cumulative sum of all prior years and months for each year, period?

I want to generate a rolling sum per Year, Period, CompanyCode, AccountNo, ProfitCenter, Cost center.

It should be calculated based on the above combination.

Could someone let me know how can we achieve this?
Please see the below example for the columns and sample data

Year	Period	CompanyCode	AccountNo	ProfitCenter	CostCenter	Amount
2015	1	Company1	1	PC1	CC1	100
2015	1	Company1	2	PC1	CC1	200
2015	2	Company1	1	PC1	CC1	100
2015	2	Company1	2	PC1	CC1	200
2020	1	Company1	1	PC1	CC1	500
2020	1	Company1	2	PC1	CC1	1000
2020	2	Company1	1	PC1	CC1	600
2020	2	Company1	2	PC1	CC1	1500

Output

Year	Period	CompanyCode	AccountNo	ProfitCenter	CostCenter	Amount	CumulativeSum
2015	1	Company1	1	PC1	CC1	100	100
2015	1	Company1	2	PC1	CC1	200	200
2015	2	Company1	1	PC1	CC1	100	200
2015	2	Company1	2	PC1	CC1	200	400
2020	1	Company1	1	PC1	CC1	500	700
2020	1	Company1	2	PC1	CC1	1000	1400
2020	2	Company1	1	PC1	CC1	600	1300
2020	2	Company1	2	PC1	CC1	1500	2900

Thanks

@sunny_talwar 

[stevedark]

Hi there,

You may be able to do it with RangeSum Above, and the right parameters.

I tend to prefer to linking data in the load script to do this kind of thing. If you take a look at this blog post then it may give you some ideas:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

The other thing you can do is to create it in Sense. Here there is an accumulate tick box, and you can then look at the code that is generate which shows how the accumulation is done, under the bonnet.

Good luck!

Steve

[sush]

Thanks for your response. I have already checked the link that you have provided but I am still not able to get what I have mentioned.

For each combination of AccountNo, CompanyCode, ProfitCenter, CostCenter I would like to calculate the sum for each Year and Period.

For example, when I select the Year - 2021 and the Month - March I would like the amount to be the sum of all prior years and months for AccountNo, CompanyCode, ProfitCenter, Costcenter.

Could you please let me know how can I achieve this?

Thanks

[stevedark]

Hi @sush 

I still believe the technique in the blog post should help you out.

You just need to create the table, from a looped set of resident loads, to build links from each month to all previous months. The table would look like this (presuming your data starts at the start of last year):

Month Agg Month
Jan-20 Jan-20
Jan-20 Feb-20
Feb-20 Feb-20
Jan-20 Mar-20
Feb-20 Mar-20
Mar-20 Mar-20

Then, rather than using Month as the dimension in your table use Agg Month. On the row with March-2020 it will actually contain the total for all months up to March-2020, due to the association.

You can also create Agg Year and Agg Period fields in the Agg Month table.

Obviously, if you have a lot of history the current month will associate with a lot of previous months, but that is what you need to it to do.

Hope that helps.

Steve