Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

12 months cumulative totals in each month

Hi,

I have a BAR chart & Straight table where for each month I need the cumulative sum of value over previous 12 months prior to that month

for example:12 months.JPG

I tried with this solution, but its not working & its effecting the Performance.
https://community.qlik.com/t5/QlikView-App-Development/sum-over-12-month-for-each-month/td-p/1159622

Is there a solution where I can do it in Script level & it should work on any selection also.

Thanks in Advance

7 Replies
Highlighted
Creator III
Creator III

Re: 12 months cumulative totals in each month

Hi,

Some days back I was having the same difficulty. You can follow below doc for you requirement.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130 

Highlighted
Partner
Partner

Re: 12 months cumulative totals in each month

Hi! Try something like

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))

Check the following posts for further info!

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

Bests,

Jaime.

Highlighted
Master II
Master II

Re: 12 months cumulative totals in each month

Hi Renuka,

Try using a rangsum function here.

rangesum(above(total sum(Sales),0,12))

Makes sure the table is sorted on Year, month.

Best Regards,
KC
Highlighted
Creator III
Creator III

Re: 12 months cumulative totals in each month

Both are doing cumulative, it is not what I want.

DATE Sales
01/03/2017 1,167
01/04/2017 407
01/05/2017 476
01/06/2017 617
01/07/2017 789
01/08/2017 823
01/09/2017 1,088
01/10/2017 706
01/11/2017 856
01/12/2017 863
01/01/2018 856
01/02/2018 971
01/03/2018 1,812
01/04/2018 357
01/05/2018 577
01/06/2018 919
01/07/2018 830
01/08/2018 632
01/09/2018 1,231
01/10/2018 1,042
01/11/2018 1,160
01/12/2018 1,035
01/01/2019 1,297
01/02/2019 1,068
01/03/2019 1,035
01/04/2019 76

 

Output should be

01/04/2019      01/03/2019
10,901          11,182

 

Hope you got this

Highlighted
Master II
Master II

Re: 12 months cumulative totals in each month

Check this, seems to be working:

the total i got is :

01/04/2019      01/03/2019
10,902          11,183

 

Best Regards,
KC
Highlighted
Creator III
Creator III

Re: 12 months cumulative totals in each month

Thank You very much, this is what I needed, but here I am getting a problem, when I select Year of FiscalYear, its show for the respective year also when bypass Year/FiscalYear it gives me for all the available months, I want to restrict for only last 12 month. And also when I select any particular month then its show its last 12 month sales. For e.g. when I select Aug-2018 it should show 3315.00 instead of 632.00

Sorry maybe it's small I am not able get it now.

Highlighted
Master II
Master II

Re: 12 months cumulative totals in each month

Hi ,

May be this:

Best Regards,
KC