Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sush
Contributor III
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

YearPeriodCompanyCodeAccountNoProfitCenterCostCenterAmount
20151Company11PC1CC1100
20151Company12PC1CC1200
20152Company11PC1CC1100
20152Company12PC1CC1200
20201Company11PC1CC1500
20201Company12PC1CC11000
20202Company11PC1CC1600
20202Company12PC1CC11500

 

Output

YearPeriodCompanyCodeAccountNoProfitCenterCostCenterAmountCumulativeSum
20151Company11PC1CC1100100
20151Company12PC1CC1200200
20152Company11PC1CC1100200
20152Company12PC1CC1200400
20201Company11PC1CC1500700
20201Company12PC1CC110001400
20202Company11PC1CC16001300
20202Company12PC1CC115002900

 

Thanks

@sunny_talwar 

 

 

3 Replies
stevedark
Luminary Alumni
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

sush
Contributor III
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

stevedark
Luminary Alumni
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