Rolling Months

[#1]

Hi!

Plz I need some help!!!

I have attached a sample app below which contains some data.

If I am looking at May 2010 data (i.e. 15 ) then this means that it is the data is uptill May (i.e. Apr and May).

Now i want a chart like the attached one.

Suppose i have Clicked the dimension Monthyear as 'Apr 2011'  Then the value for this is calculated as: May2010+Jun 2010+......Apr2011.

My X axis will contain last 12 Month from the selected dimension Value.

[pat_agen]

hi,

you are looking for a rolling twelve month figure for each month. Sridhar pointed you in a very good direction. The script will need adapting - your starting point is different and you want a RollingTwelveMonth instead of 3Month but all the mechanics are there. Basically you will need to create an AsOfCalendar which when linked to your fact table will allow you create your chart.

Try the document from John W. and see how far it gets you.

[pat_agen]

hi,

I don't know if you find your solution yet.

Attached is one version adapted from a John Witherspoon answer. sorry I don't have the link here. It is slightly different from the version posted by Sridhar but builds an AsOf Calendar as  in that example.

I've adapted it to take your input data as the jump in point. Works perfectly (I haven't seen much of John W's stuff that doesn't to tell the truth! )

Anyway - hope it helps.

[#4]

actually I am not able to get 'for the month data'.

The previous func is not working.

For every monthYear I may have more than one value of Uptodata and the months are not sorted....therefore the previous is not working as I want.

Actually i am having data some what like this:

LOAD * INLINE [

    Monthyear,Uptodata,Month

    Apr 2010, 10,1

    Apr 2010, 20,1

    Jun 2010, 20,3

    Jun 2010, 40,3

    Jul 2010, 22,4

    May 2010, 15,2

    May 2010, 10,2

    Sep 2010, 35,6

    Nov 2010, 50,8

    Nov 2010, 10,8

    Jan 2011, 60,10

    Feb 2011, 63,11

    Aug 2010, 30,5

    Apr 2011, 20,1

    May 2011, 25,2

    Oct 2010, 45,7

    Mar 2011, 70,12

    Jun 2011, 30,3

    Dec 2011, 65,9

    Aug 2011, 40,5

    Sep 2011, 43,6

    Dec 2010, 55,9

    Oct 2011, 50,7

    Jul 2011, 32,4

    Nov 2011, 60,8

    Jan 2012, 70,10

    Feb 2012, 80,11

    Mar 2012, 90,12

];

[#5]

Check this attached application.

-Sridhar

[#6]

Thanks a lot Sridhar!!!

I am using somewhat like the same code.In dummy application Its working fine...

Let me check if its working in my Main application also.

Then i willcome back!!!

[#7]

  Hi guys, By using all the suggestions , I am able to achive what I wanted.

Thanks to all for Help!!!!!!!

My chart displays as the given attachment.But Now I want the sort order to be changed.Means it should first display Jan2010,Feb2010......Dec 2010 and the respectives values for each month should not change.

I am using the following code in my expression.

=(RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

NewDaily.Cum.EILR),0,12))

/

((

RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

(NewDaily.Cum.GWP)),0,12))

-

RangeSum(below(sum({<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}NewDaily.Cum.1103),0,12))

)

-

RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

NewDaily.Cum.EILR1),0,12))

))

My dimension is :DailyDashboardMonthYear

Variable1:=Max(DailyDashboardDate)

Variable2:=Addmonths(DailyDashboardDate,-23)