topic Re: Need help generating Rolling 12 months chart in QlikView

Need help generating Rolling 12 months chart

mgavidia — Fri, 13 Sep 2013 00:19:19 GMT

Hello,

I'll appreciate some assistance to convert the attached chart to a Rolling 12 month report.

I have revieweed some samples shown in the community but, after a couple of days of trying different scenarios, with different formulas, I still cannot generate the Rolling 12 chart. It is problably a simple thing however, I cannot figure it out.

Thank you very much for any tips that you can provide.

Miguel Gavidia

Los Angeles, CA

Re: Need help generating Rolling 12 months chart

fernando_tonial — Fri, 13 Sep 2013 01:45:48 GMT

Hi, you have any problem with the data.

Try this expression

Sum({<TransPostedDate = {">=$(=MonthStart( AddMonths(Max(TransPostedDate),-12)))<=$(=Max(TransPostedDate))"}>} #MoveInCounter)

Best Regards.

Tonial

Re: Need help generating Rolling 12 months chart

yevgeniy — Tue, 17 Sep 2013 14:06:39 GMT

Hello,

Change expressions

Sum({1<MoveInYear = {"$(=max(Year)-1)"}>}#MoveInCounter)

if(Sum({1<MoveInYear = {"$(=max(Year))"}>}#MoveInCounter) > 0,

Sum({1<MoveInYear = {"$(=max(Year)-1)"}>}#MoveInCounter),'0')

Re: Need help generating Rolling 12 months chart

israrkhan — Tue, 17 Sep 2013 14:17:35 GMT

hi,

this link will give you answer in detail:

http://community.qlik.com/docs/DOC-4252

Re: Need help generating Rolling 12 months chart

Tue, 17 Sep 2013 14:20:19 GMT

Hi Miguel

It would be a good idea to change the X-axis so that it looks at MMM-YY, as otherwise should you select a mid year month then Jan-14 will appear before Jul-13 and there will be a disconnect between months.

You could solve this with a fancy formula in the sort tab, but for clarity's sake it would be best to show the month also.

Regards

Steve

Re: Need help generating Rolling 12 months chart

Thu, 19 Sep 2013 12:29:42 GMT

you can do this with Monthago function...

Re: Need help generating Rolling 12 months chart

tresB — Thu, 19 Sep 2013 12:32:27 GMT

Hello Sneha,

Is there really a such(monthago()) function in qv?

Re: Need help generating Rolling 12 months chart

Fri, 20 Sep 2013 08:28:11 GMT

Hi tresesco,

Actually its not direct function nut we created on field called Monthago in the script.

Then we can that in the chart to have rolling months data. like rolling 12 months, rolling 6 months etc.

12*(Year(Today())-Year(Date)) +Month(Today())-Month(Date) as MonthsAgo,

And we used this in chart expression as

For rolling 12 months

Sum({$<MonthsAgo={">=0<12"}>} [Actual Amount])

For previous months we can writelike this:

Sum

({$<MonthsAgo={">=12<18"}>} [Actual Amount])

it will give prevoius 6 months data.....

You can use this approach for rolling 12 months...

Re: Need help generating Rolling 12 months chart

Fri, 20 Sep 2013 09:38:20 GMT

Sneha Topre (to tresesco) 1 hour ago