Display past 12 months rolling

jlampard40 — Thu, 12 Sep 2019 15:00:16 GMT

Hi guys. I have 5 years worth of data for my project. I have no problem creating a chart which displays these five years. However, the last month (currently September) is only part way through and I'd like to create a chart which takes the past 12 months of full data and rolls it back every 12 months for the past 5 years.

So - I'd like to currently display:

September 2014 - August 2015
September 2015 - August 2016
September 2016 - August 2017
September 2017 - August 2018
September 2018 - August 2019

Instead, currently, it shows:

April 2014 - March 2015
April 2015 - March 2016
April 2016 - March 2017
April 2017 - March 2018
April 2018 - March 2019
April 2019 - currently August 2019 (!!)

So, as you can imagine - the last bar of my chart looks tiny, compared to the rest of the full 12 months worth of data. Is there any way of telling Qlikview to just display the past 12 months of full data (rolling back 5 years) from whichever point of the year we're currently in?? So we have a full 12 months data for past 5 years - not worried about whether it's a financial year or calendar year. We just want to show a full 12 months for 5 years wherever we are in the year!

The fields I have to play with are:

AD_CalYr (this is the year - so 2014, 2015, 2016, 2017, 2018, 2019; clearly 2019 is going to show limited months)

AD_CalYrMth (this is in format YYY-MM) - so we go as far back as 2014-04 and up to 2019-08 as full months.

Hopefully you can see my problem and somehow help. I just want to show 12 months rolling for past 5 years worth of data, wherever we are in the year. Many, many thanks!

Re: Display past 12 months rolling

Brett_Bleess — Wed, 25 Sep 2019 12:47:44 GMT

James, about the best I have are some links from the Design Blog area that may give you some ideas, but there are likely other prior Community posts as well that may give you what you need as well, so if you have not searched, I would recommend trying that as well.

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

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

Those are a couple that seem to be potentially on the right track, here is the main blog link to where you can search on your own as well:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

My post will kick things back up to the current timeframe as well, so someone else may now chime in that is better at development than am I, and they may have a better idea... Generally, if you can attach a sample app with some real data etc., it makes it much easier for folks to try to help you too, just FYI.

Regards,
Brett

topic Re: Display past 12 months rolling in QlikView

Display past 12 months rolling

Re: Display past 12 months rolling