This discussion is archived
4 Replies Latest reply: Oct 6, 2012 6:08 AM by 924768 RSS

Rolling Sum in OBIEE 10G

924768 Newbie
Currently Being Moderated
Hi
I have an requirement where in we need to calculate Demand of an particular item for past 2 years plus 3months forecast * 4 (i.e demand for next 1 year) in simple words:
(Past 2 year demand + (3 months forecast *4))
How can I achive this in OBIEE 10g.
I have read that there is periodrolling function in OBIEE 11g. is there any such function in OBIEE 10g
Regards
Sameer
  • 1. Re: Rolling Sum in OBIEE 10G
    Ahsan Shah Expert
    Currently Being Moderated
    check this: http://shivabizint.wordpress.com/2008/09/19/rolling-months-data-for-year-month-prompt-in-obiee/

    if helpful, pls mark correct or helpful
  • 2. Re: Rolling Sum in OBIEE 10G
    924768 Newbie
    Currently Being Moderated
    Thanks Ahsan
    But can I get an single field. I need some thing like this:

    Demand for past 2 year Forcast for next 3 months *4 Total Demand
    100000 100*4 = 400 100400

    Now I want to compare Total demand with my Inventory.

    Is there a way by which I can create a single field in RPD or Anwser to give (Demand for past 2 years + forcast for next year) or two seperate fields as shown above.

    Regards
    Sameer
  • 3. Re: Rolling Sum in OBIEE 10G
    Andy Coates Journeyer
    Currently Being Moderated
    As you say, the periodrolling function is the way to do this but it is only availabel in 11g. The requirement can however be met in OBIEE 10g using the Ago time series function. However, I would warn you that nesting such measures can give rise to performance issues on large data sets; you may wish to push the complexity down to the warehouse by building the logic into the ETL.

    Assumption is that the report will need to be prompted by month. It would be nice if we could use the Ago function for future periods by using a negative offset! Instead, one has to shift the prompted period into the future.

    Here is an approach (there are others) that I might take to prove the concept. As I say, my preference would probably be to push this down into the Warehouse to reduce the work being done at runtime.

    Let's say that the current period is JUN-2012. We therefore want to add up the demand for this period along with the 23 preceding periods. In addition, we want to also add the demand forecast for the subsequent three periods (and multiply this figure by four). In order to do this, we have to prompt the request using the last forecast period e.g. the current period plus three. This will allow us to use the Ago function as all periods will then be historic (with respect to the prompted period). For example:-

    Period     Ago Function Offset     Comment
    Jul-10     26
    Aug-10     25
    Sep-10     24
    Oct-10     23
    Nov-10     22
    Dec-10     21
    Jan-11     20
    Feb-11     19
    Mar-11     18
    Apr-11     17
    May-11     16
    Jun-11     15
    Jul-11     14
    Aug-11     13
    Sep-11     12
    Oct-11     11
    Nov-11     10
    Dec-11     9
    Jan-12     8
    Feb-12     7
    Mar-12     6
    Apr-12     5
    May-12     4
    Jun-12     3               Current Period
    Jul-12     2               Forecast
    Aug-12     1               Forecast
    Sep-12     0               Forecast (prompted period)

    Steps:-

    1) Create time series measures using the ago function with the above offsets e.g.

    AGO(Core."Fact - Demand".Demand, Core."Date - Fiscal Calendar"."Fiscal Period", 0)
    AGO(Core."Fact - Demand".Demand, Core."Date - Fiscal Calendar"."Fiscal Period", 1)
    AGO(Core."Fact - Demand".Demand, Core."Date - Fiscal Calendar"."Fiscal Period", 2) etc. up to 26

    2) Create a calculated measure adding up measures with offsets 3 through 26, resulting in a measure for the last 2 years
    3) Create a calculated measure adding up the three forecast measures and multiply this by 4
    4) Create a calculated measure adding 2) and 3)

    Obviously the user will need to prompt the report with the latest forecast period (Sep-12). If this is not acceptable then one could employ workarounds in the prompt e.g. using presentation variables and the timestampadd function.

    Hope this helps, please mark as helpful/answered if so.

    Andy.
  • 4. Re: Rolling Sum in OBIEE 10G
    924768 Newbie
    Currently Being Moderated
    Thanks Andy for your reply

    But I didnt get how to include negative offset in ago function to calculate future period forecast value.
    I will have to do this because the forecast and demand value will come from different fact table.
    I can make one logical column for demand (sum of month ago from 0 to 24 month) & another column for forcast (of next 3 month)
    but for this I need to start the period for Demand & forecast from same point 'Current Month' (i.e from Month of Jun-12 as per your example)
    and prompting with latest forecast period is not acceptable.

    How can I achive this?

    Regards
    Sameer

Legend

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