Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi! Try something like
sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))
Check the following posts for further info!
Calculating rolling n-period totals, averages or other aggregations
Bests,
Jaime.
Hi Renuka,
Try using a rangsum function here.
rangesum(above(total sum(Sales),0,12))
Makes sure the table is sorted on Year, 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
Check this, seems to be working:
the total i got is :
01/04/2019 01/03/2019
10,902 11,183
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.
Hi ,
May be this: