14 Replies Latest reply: Dec 20, 2012 1:08 PM by 978780 RSS

    Periodic Rolling 6 months

    978780
      Hi All,

      I need the help in calculating the forecast for next 6 months which need to be happen dynamically in obiee 10g. It very straight forward in 11g but we are using 10g Its bit urgent please anyone can help me

      Example: I have report with three column, Year, Month, Forecast

      Year Month Forecast
      2009 Jan 100
      2009 Feb 200
      2009 Mar 300
      so on

      I want the fourth column which need to aggregate for next three months forecast based on the month example

      Year Month Forecast 3months forcast
      2009 Jan 100 600
      2009 Feb 200 900
      2009 Mar 300 800
      2009 Apr 400
      2009 May 100
      so on

      Thanks
      Praveen
        • 1. Re: Periodic Rolling 6 months
          sb92075
          975777 wrote:
          Hi All,

          I need the help in calculating the forecast for next 6 months which need to be happen dynamically in obiee 10g. It very straight forward in 11g but we are using 10g Its bit urgent please anyone can help me
          Please clarify why it is URGENT for me to solve this issue for you.


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Periodic Rolling 6 months
            Srini VEERAVALLI
            Use MSUM for Forecast column

            ex:
            MSUM (Forecast, 3)

            This would sum 3 rows from current and goes like that.

            Appreciate if you mark as correct :)

            Edited by: Srini VEERAVALLI on Dec 8, 2012 09:09 PM

            ---------Update---------

            Looks like requirements are changed after my suggestion, in your post you haven't mentioned that its about average, tweak it you'll get it.

            If helps pls mark.

            Edited by: Srini VEERAVALLI on Dec 8, 2012 10:33 PM
            • 3. Re: Periodic Rolling 6 months
              978780
              Thanks Srini for the quick response but actually Mavg(forecast, 3) will do it for the previous months take average. I want to the add future months & take the avg

              Example
              Month Forecast Mavg(forcast)
              Jan 100 100
              Feb 200 150
              March 300 200

              But I want this in this fashion

              Month Forecast furtheforcast for 3 months Avg(Further forcast)

              Jan 100 600 200
              Feb 200 900 300
              March 300 900 300
              April 400
              May 200

              so on

              Thanks
              Praveen
              • 4. Re: Periodic Rolling 6 months
                978780
                Hi

                Please anyone can provide me the perfect solution its bit urgent please.

                Thanks
                Praveen
                • 5. Re: Periodic Rolling 6 months
                  David_T
                  975777 wrote:
                  Hi All,

                  I need the help in calculating the forecast for next 6 months which need to be happen dynamically in obiee 10g. It very straight forward in 11g but we are using 10g Its bit urgent please anyone can help me

                  Example: I have report with three column, Year, Month, Forecast

                  Year Month Forecast
                  2009 Jan 100
                  2009 Feb 200
                  2009 Mar 300
                  so on

                  I want the fourth column which need to aggregate for next three months forecast based on the month example

                  Year Month Forecast 3months forcast
                  2009 Jan 100 600
                  2009 Feb 200 900
                  2009 Mar 300 800
                  2009 Apr 400
                  2009 May 100
                  so on

                  Thanks
                  Praveen
                  Well, I'm at work now and tested my theory. Turns out it didn't work. I see what you are saying (I am referring to Praveen's post directly below this one.)

                  I do have a solution and will post it soon...

                  Edited by: David_T on Dec 11, 2012 9:50 AM
                  • 6. Re: Periodic Rolling 6 months
                    978780
                    Thanks David for the response. But its giving only one month result
                    CASE WHEN table.Month = 'Jan' THEN IFNULL(FILTER(table.Forecast USING table.Month = 'Jan'),0) + IFNULL(FILTER(table.Forecast USING table.Month = 'Feb'),0) + IFNULL(FILTER(table.Forecast USING table.Month = 'Mar'),0) it not adding rest of the months.

                    Thanks
                    Praveen
                    • 7. Re: Periodic Rolling 6 months
                      978780
                      Thanks David for response. I will be waiting for your post. Mean while I try other approaches as well.

                      Thanks
                      Praveen
                      • 8. Re: Periodic Rolling 6 months
                        David_T
                        Okay, here is the solution. I'll give you enough to start it, because after a certain point, it's just a matter of "copy and paste," etc. And this one I tested so it works... :)

                        1) Create a small report with the following columns: Year, Month, and two instances of your Forecast column.

                        2) On the Month, column, click on the fx button and enter this:

                        CASE WHEN table.Month IN ('JAN', 'FEB', 'MAR') THEN '01-JAN' END

                        3) On the Forecast column, click on the fx button and then click on the Filter button. Go to your Month column in the left panel picklist and set it equal to 'JAN' so you have this:

                        FILTER(table.Forecast USING (table.Month = 'JAN'))

                        4) On the second instance of your Forecast column, click on the fx button and enter this:

                        FILTER(table.Forecast USING (table.Month = 'JAN')) + FILTER(table.Forecast USING (table.Month = 'FEB')) + FILTER(table.Forecast USING (table.Month = 'MAR'))

                        (You can add the IFNULL function if you want...)

                        5) On the main filter of the report, add a filter on table.Month and set it equal to 'JAN', 'FEB', 'MAR'

                        Run this query to test. You will see that it has the first line of what you are looking for...

                        6) Back in Criteria mode, click on the "Combine with Similar Request" button.

                        7) Click on the "box" to the right of the query and click "Copy." Click it again and select "Paste." Then delete the third query that is blank. This is a shortcut to creating your second query. The first query becomes a template so you don't have to do everything from scratch.

                        8) Modify steps 2, 3, and 4 for February forecast. Basically, you will delete one month and add the next as well as changing the names to reflect the current month's forecast. The reason I wanted you to name the month in step 2 as '01-Jan' and so on is because this is a CHAR column and if you don't do this, you will have a sorting issue.

                        Okay, I think you get the idea. Once you repeat these steps, you will have what you are looking for. Note the additional modifications necessary when the Year changes from "current year" to "next year."

                        Okay, that's it. It works so try it.
                        • 9. Re: Periodic Rolling 6 months
                          978780
                          Hi David,

                          Your approach work for certain level but final goal was something different. Which posted clearly now. which need to be happen dynamically this metric calculation need to done at back end so that easily can use for other reports as well. I thought if i able to add next 6 months data I can achieve my goal it something different.Please can you suggest any other idea. It very important metric to business please help me


                          MOH need to calculate forumula
                          Project Available Balance / Avg(Forecast Qty for next 6 months)

                          1.Report Layout
                          Year     Month     Project Available Balance     Forecast     6 Month Forecast     Avg 6 Month     MOH
                          2012     JAN     1000     10     160     160/6 = 26.66     1000/26.66 = 37.5
                          2012     FEB     2000     20     270     270/6 = 45     2000/45 =44.4
                          2012     MAR     3000     30     330     330/6 = 55     3000/55 = 55.5
                          2012     APR     2000     40     So on     So on     So on
                          2012     MAY     3000     50               
                          2012     JUN     4000     60               
                          2012     JUL     5000     70               
                          2012     AUG     6000     80          

                          Thanks
                          Praveen

                          Edited by: 975777 on Dec 12, 2012 12:52 PM
                          • 10. Re: Periodic Rolling 6 months
                            977284
                            Can you put the clear column heading and test data so that it is readable?

                            Can I assume the columns are as follows?

                            Year
                            Month
                            "Project Available Balance"
                            "Forecast 6 Month"
                            "Forecast Avg 6 Month"
                            MOH

                            Thanks
                            Sundar
                            • 11. Re: Periodic Rolling 6 months
                              978780
                              Hi Sundar,

                              Yes Columns you identified is right.But once again I will type here


                              Year Month Project_Available_balance Forecast 6month_Forecast Avg_6month_Forecast MOH

                              2010 Jan 1000 10 160 160/6 = 26.66 1000/26.66 =

                              2010 Feb 2000 20 270 270/6 = 45 2000/45 =

                              2010 Mar 3000 30 330 330/6 = 55 3000/55 =

                              2010 Apr 2000 40 so on

                              2010 May 4000 50

                              2010 Jun 5000 60

                              2010 Jul 4000 70

                              2010 Aug 2000 80

                              Main challenge is add the 6 months forecast forward rolling dynamically based on the month in obiee.

                              Thanks
                              Praveen
                              • 12. Re: Periodic Rolling 6 months
                                David_T
                                975777 wrote:
                                Hi David,

                                Your approach work for certain level but final goal was something different. Which posted clearly now. which need to be happen dynamically this metric calculation need to done at back end so that easily can use for other reports as well. I thought if i able to add next 6 months data I can achieve my goal it something different.Please can you suggest any other idea. It very important metric to business please help me


                                MOH need to calculate forumula
                                Project Available Balance / Avg(Forecast Qty for next 6 months)

                                1.Report Layout
                                Year     Month     Project Available Balance     Forecast     6 Month Forecast     Avg 6 Month     MOH
                                2012     JAN     1000     10     160     160/6 = 26.66     1000/26.66 = 37.5
                                2012     FEB     2000     20     270     270/6 = 45     2000/45 =44.4
                                2012     MAR     3000     30     330     330/6 = 55     3000/55 = 55.5
                                2012     APR     2000     40     So on     So on     So on
                                2012     MAY     3000     50               
                                2012     JUN     4000     60               
                                2012     JUL     5000     70               
                                2012     AUG     6000     80          

                                Thanks
                                Praveen

                                Edited by: 975777 on Dec 12, 2012 12:52 PM
                                Before I proceed further, you must understand what an earlier poster asked: this may be urgent for you, but it is not urgent to us so saying, "this is urgent!" actually dissuades many from tackling a problem b/c it implies that we must rush to help you.

                                Second, this is what happens when you don't give all the details. If you want to get a solution that works, you need to give all the pertinent information. It bothers me when people give the scantiest of details, ask for help (and hurry! I need it right now!!), and after providing it, say, oh, it doesn't work because I have other requirements I didn't tell you. Arrggh!

                                In your case, I just basically used a lot of time thinking, designing and testing a solution that doesn't meet your solution only because you didn't give the details. And now you say, "I posted what I really want now" and expect me/us to jump on this because it's urgent?

                                Why don't you start by marking helpful posts? It shows a level of appreciation for what has been done when work and effort have been put into finding a solution.
                                • 13. Re: Periodic Rolling 6 months
                                  978780
                                  Hi David,

                                  I'm Sorry David I was not mean to say that. But anyways Sorry.

                                  Thanks
                                  Praveen
                                  • 14. Re: Periodic Rolling 6 months
                                    978780
                                    Hi All,

                                    Still in search for solution please any one can help me.

                                    Thanks
                                    Praveen