Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Could you please any one guide me how to add last 12 months rolling data in month wise.
we have data 2016 to 2019 August
Dimension:- Market and Year_Month
Measure- Sales
This first table we are showing last 12 months data Month wise.
Market | Sep-18 | Oct-18 | Nov-18 | Dec-18 | Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 |
Benelux | 20 | 10 | 20 | 10 | 20 | 10 | 20 | 10 | 10 | 30 | 10 | 20 |
So in 2nd Table we want to show Aug-2019 data should be addition of last 6 months data from 1st table.
same way other months data should be last 6 months data(That's highlighted in bold)
Right now i provided sample data only
Market | Sep-18 | Oct-18 | Nov-18 | Dec-18 | Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 |
Benelux | 20 | 10 | 20 | 10 | 20 | 90 | 90 | 90 | 80 | 100 | 90 | 100 |
Try something like this
sum( aggr( rangesum( above( total sum( {<Year=, Month=, Year_Month=>} Sales),0,6)),[Year_Month]))
An alternative to my previous suggestion is to look into the as of table. With the as of table your expression gets simpler, you don't need to bother with all the rangesums and aggrs.
See duplicate post too: https://community.qlik.com/t5/QlikView-App-Development/How-to-add-last-6-months-rolling-data-in-mont...
Regards,
Brett