15 Replies Latest reply: Jun 9, 2011 5:25 AM by Erika Jain RSS

    Rolling Months

      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.

       

        • Re: Rolling Months

          Hi,

           

          Have a look at the attached demo application which has been created by John Witherspoon


          This application explains about the functionality of Rolling 12 months, Qtr and YTD in detail.

           

          Hope this helps you.

           

          - Sridhar

            • Rolling Months

              Thanks for the help!

              But I don't want this.

               

              My actual problem is How to convert' Upto data' into 'for the Month data'.

               

              if  Uptill May 2010 data (i.e. 15 (April + may)) and April 2010 data is 10.

              Then this means that April data is 10. May data is 5 (In month may Data is 5 only).

                • Rolling Months
                  Patrick Laredo

                  hi,

                   

                  You want to isolate each month's total from the running total in your original dataset. is that correct?

                   

                  put this preceeding load before your load inline statement.

                   

                  load

                  Monthyear,

                  Uptodata,

                  if(left(Monthyear,3)='Apr',Uptodata,Uptodata - previous(Uptodata)) as MonthData;

                   

                  From your data it looks as if your accumulated total resets itself in April so we test for April.

                   

                  MonthData shoudl give you what you require.

                   

                  have I understood your request correctly?

                    • Re: Rolling Months

                      Thanks a lot  Pat & Rahul !!!!!.

                        Its working perfectly in the sample data which I have uploaded.

                      Hope it works in my Main application also.!!!

                      can u solve the second issue also.....The chart that I want to prepare.

                        • Rolling Months
                          Patrick Laredo

                          hi,

                           

                          could you give some more clarification about your requirements.

                           

                          Your initial post was not very clear and Sridhar gave you the reply I was thinking about. It wasn't till you replied to that mail that I understood you requirement. Same goes for the graph - I don't see what you are trying for.

                            • Rolling Months

                               

                               

                              i have a code for what I want...

                              let me try myself....

                              Thanks to all for there help!!!

                              • Rolling Months

                                 

                                 

                                i am attaching a chart.

                                May be now its clear.

                                • Re: Rolling Months

                                  i am attaching a chart.

                                  May be now its clear.

                                    • Rolling Months
                                      Patrick Laredo

                                      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.

                                        • Re: Rolling Months
                                          Patrick Laredo

                                          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.

                                            • Rolling Months

                                              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

                                              ];

                                                • Re: Rolling Months

                                                  Check this attached application.

                                                   

                                                  -Sridhar

                                                    • Rolling Months

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

                                                        • Re: Rolling Months

                                                            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)

                                                           

                                        • Re: Rolling Months
                                          Rahul Gupta

                                          Hey Hi,

                                           

                                          Kindly find the attached file.

                                           

                                          Regards