Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

mgavidia
Contributor

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

8 Replies
fernando_tonial
Valued Contributor

Re: Need help generating Rolling 12 months chart

Hi, you have any problem with the data.

Try this expression

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

Best Regards.

Tonial

yevgeniy
Contributor

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')

israrkhan
Valued Contributor II

Re: Need help generating Rolling 12 months chart

hi,

this link will give you answer in detail:

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

Not applicable

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

Not applicable

Re: Need help generating Rolling 12 months chart

you can do this with Monthago function...

MVP
MVP

Re: Need help generating Rolling 12 months chart

Hello Sneha,

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

Not applicable

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

Not applicable

Re: Need help generating Rolling 12 months chart

Community Browser