# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor III

## Cumulative sum for all prior years per period - Rolling sum

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

Labels (4)

• ### sum

3 Replies
Luminary Alumni

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

Contributor III
Author

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

Luminary Alumni

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