Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, you have any problem with the data.
Try this expression
Sum({<TransPostedDate = {">=$(=MonthStart( AddMonths(Max(TransPostedDate),-12)))<=$(=Max(TransPostedDate))"}>} #MoveInCounter)
Best Regards.
Tonial
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')
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
you can do this with Monthago function...
Hello Sneha,
Is there really a such(monthago()) function in qv?
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...