14 Replies Latest reply on Dec 20, 2012 7:08 PM by 978780

# Periodic Rolling 6 months

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

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

Thanks
Praveen
• ###### 5. Re: Periodic Rolling 6 months
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
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
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
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
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
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
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
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
Hi David,

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

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

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

Thanks
Praveen