8 Replies Latest reply: Sep 20, 2013 5:38 AM by Sneha Topre

# Need help generating Rolling 12 months chart

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

Hi, you have any problem with the data.

Try this expression

Best Regards.

Tonial

• ###### Re: Need help generating Rolling 12 months chart

Hello,

Change expressions

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

on

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

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

• ###### Re: Need help generating Rolling 12 months chart

hi,

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

• ###### Re: Need help generating Rolling 12 months chart

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

you can do this with Monthago function...

• ###### Re: Need help generating Rolling 12 months chart

Hello Sneha,

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

• ###### Re: Need help generating Rolling 12 months chart

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...