Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE Time Series Function

Received Response
21
Views
12
Comments
3604866
3604866 Rank 2 - Community Beginner
edited Aug 13, 2024 5:53PM in Oracle Analytics Cloud and Server

Hi All,

I am getting below data using time series function to date in OBIEE. Here I providing level “ALL_YEARS” to get get my project to date data i.e from the beginning to current period.

Below data I am able to get.

Period Project Employee Original_Billing PROJTD_BILLING(Getting using time series)

2016-07 ABC 123 10 10

2016-08 ABC 123 NULL 10

2016-09 ABC 123 NULL 10

2016-10 ABC 123 20 30

2016-11 ABC 123 -5 25

2016-12 ABC 123 NULL 25

2017-01 ABC 123 NULL 25

2017-02 ABC 123 35 40

I want column Period_Billing(previous period PROJTD_BILLING – Current period PROJTD_BILLLING)

Period Project Employee Original_Billing PROJTD_BILLING Period_Billing(How to achieve??)

2016-07 ABC 123 10 10 10

2016-08 ABC 123 NULL 10 0

2016-09 ABC 123 NULL 10 0

2016-10 ABC 123 20 30 20

2016-11 ABC 123 -5 25 -5

2016-12 ABC 123 NULL 25 0

2017-01 ABC 123 NULL 25 0

2017-02 ABC 123 35 40 -15

Thanks In Advance!!!!

«1

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics & AI Strategist

    Hi,

    assuming you have access to the rpd my preferred method for your requirement would be to; -

    Use AGO functionality to build your new measures for last period as a logical function

    Create a new logical measure that uses your two time series measures as the basis of your X - Y calculation

    If I have not described your requirement then kindly clarify with more detail.

  • 3604866
    3604866 Rank 2 - Community Beginner

    Hello Robert,

    Thanks for the update but I kind use ago function on my existing measure PROJTD_BILLING, the reason is in PROJTD_BILLING I am passing "ALL_YEAR" level, so it will include all data set.

    So when i am using ago with same level there is no data set that wil not return any value.

    And i cant use ago with different level as obiee doesn't accept it, it show error.

    Regards,

    Parag

  • Robert Angel
    Robert Angel Rank 8 - Analytics & AI Strategist

    Okay, you have to use the AGO measures with a period that pins them to what they are AGO relative to, BUT, you could solve this from the other direction by creating a FULL YEAR measure by duplicating the base member and pinning it to YEAR or TOTAL level (I am not clear what ALL_YEAR means, if it is full year then the former, if all years then the latter) on the time dimension hierarchy.

    This then makes that measure invariant below the level of detail you have pinned it to, so if TOTAL it will show the sum of all years, if YEAR it will give the total for the year relative to the detail being shown (so for this month the sum of 2017, assuming Gregorian calendar in your time dimension)

    Make sense?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Your examples is weird ... It works except for the end:

    2017-01: billing = NULL, to date billing = 25

    2017-02: billing = 35, to date billing = 40

    25 + 35 is supposed to give you 60 as to date billing for 2017-02

    So if you explain how your to date can be 40 instead of 60 then it will be possible to solve your question.

    Assuming it's really 60 (and the 40 is a mistake) than what you look for is just exactly the same as the billing of the current period (where NULLs are 0) with a changed signed.

    Because (TODATE of 2017-01) - (TODATE of 2017-02) = (value of 2017-02) * -1

    And your example of that column is also wrong ....

    Or there is something you aren't saying and formulas aren't really as described and the example is just wrong in few places.

  • 3604866
    3604866 Rank 2 - Community Beginner

    Thanks for your time.

    But in my case ALL_YEARS means all year not a particular year.

    And whenever I want to use ago function with above already created measure "PROJTD_BILLING" (calculated for all_year),it will not left with any dataset.

    Hope I am clear!!!.

    Regards,

    Parag

  • Robert Angel
    Robert Angel Rank 8 - Analytics & AI Strategist

    You are clear, then as I suggested pin a copy (drag / drop or copy it on to the hierarchy level) of your base measure to TOTAL in your time dimension hierarchy and rename it as appropriate, that will do what you describe, and can then be used in conjunction with time series measures, but the time series measures still need time detail in the analysis to work, but this will not effect the result that the pinned measure shows, it will not change as the years / months / days do.

  • 3604866
    3604866 Rank 2 - Community Beginner

    Hi Gianni

    Thanks for the update

    You are correct

    25 + 35 is supposed to give you 60 as to date billing for 2017-02.

    And I also want calculate current period billing only but not directly as you mention.

    (TODATE of 2017-01) - (TODATE of 2017-02) = (value of 2017-02) * -1

    I want it from already calculate column "PROJTD_BILLING" to avoid some rounding and other problem.

    Any help on this really appreciated.

  • 3604866
    3604866 Rank 2 - Community Beginner

    Yes but as I already mention ago on existing measure will not give me any data because there no data set left for ago.As i already using level all_year(means all year from project started to current period)

    in column "PROJTD_BILLING".

    So AGO("PROJTD_BILLING","ALL_YEARS",1) will not result any data.

    Beacause

    PROJTD_BILLING =TODATE("orginal_billings","ALL_YEARS").

    Hope this is more clear.

  • Robert Angel
    Robert Angel Rank 8 - Analytics & AI Strategist

    AGO ALL YEARS makes no sense (give me one period before everything?!)

    Your time period based measures need to reference the base measure.

    So you need the base measure to use generally, and to use as the basis of your time period.

    You need a copy, which is pinned to TOTAL_YEAR and so always gives the same number.

    You can then create as many logical measures as you like that reference the above as part of their formula.

  • 3604866
    3604866 Rank 2 - Community Beginner

    Hi,

    Thanks for the update.

    Can you please provide some example or more explanation.

    Thanks in advance.