This discussion is archived
14 Replies Latest reply: Dec 20, 2012 11:08 AM by 978780 RSS

Periodic Rolling 6 months

978780 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi

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

    Thanks
    Praveen
  • 5. Re: Periodic Rolling 6 months
    David_T Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi All,

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

    Thanks
    Praveen

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points