12 months cumulative totals in each month

[renuka_sasikumar]

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:

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

[miskinmaz]

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 

[jaibau1993]

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.

[jyothish8807]

Hi Renuka,

Try using a rangsum function here.

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

Makes sure the table is sorted on Year, month.

[renuka_sasikumar]

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

[jyothish8807]

Check this, seems to be working:

the total i got is :

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

[renuka_sasikumar]

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.

[jyothish8807]

Hi ,

May be this: