Saturday 8 January 2011

ago function in obiee 11g answers

One thing that I'm really pleased about in OBIEE 11g the ability to use time series analysis functions in Answers. In 10g this was reserved for the repository tool, which meant that the number and type of ago functions needed to be well defined by the business which isn't generally the case given there are practically limitless combinations.

The syntax of the the ago function as documentation in Answers is:
AGO(expr, time_level, offset)

However, as with the administration tool, there is a overloaded version of AGO:
AGO(expr, offset)

which uses the grain of the Answers query as the level.

As an example using Sample Sales lite the function AGO("Base Facts"."Target Revenue",1) gives the revenue one time period ago. If the report is at the year level then that will of course be one year ago:


What I don't like about the feature is that there is no way to select the time level - this is really user unfriendly and not at all intuitive. It seems rather half done.

If like me you tried the dimesnion and got the error nQSError: 27037, the syntax is to use the time hiearchy level:

AGO("Base Facts"."Revenue", "Time"."Time Hierarchy"."Year", 1)

Note the time hierarchy needs to be exposed to the presentation layer.

This way you can have a report at say quarter level and compare the revenue of a year ago quarter by quarter:

Posted by at

3 comments:

  1. starpageup11 September 2015 at 13:45

    Awesome......

    If you don't have Time Hierarchy exposed to Presentation and you try to use the AGO function with time_level you will basically get below error:

    "
    Formula syntax is invalid.
    [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27037] Unresolved level: "month".
    "

    In such cases, the overloaded AGO function without time_level argument should be used and OBIEE would automatically determine time_level from the report query grain.

    ReplyDelete
  2. maegansackett25 February 2022 at 06:33

    The first slot machine with a progressive jackpot
    Progressive jackpots were found 잭팟 among the 가상 화폐 추천 first casino games to 텐벳 먹튀 feature progressive jackpots The only casino with progressive jackpots to Nov 사카미치 마루 12, 2021 · Uploaded 홀덤족보 by PlayMate

    ReplyDelete
  3. rafellewaas3 March 2022 at 08:18

    How titanium tube works - Baojititanium
    This 서산 출장샵 simple 태백 출장마사지 system is 서귀포 출장샵 very easy to titanium tubing design, although it is an expensive way of making sure you will not have any 남양주 출장샵 This system is a perfect way to

    ReplyDelete

Subscribe to: Post Comments (Atom)