12 Replies Latest reply on Dec 18, 2017 1:17 PM by 3604866

# OBIEE Time Series Function

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

• ###### 1. Re: OBIEE Time Series Function

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.

• ###### 2. Re: OBIEE Time Series Function

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

• ###### 3. Re: OBIEE Time Series Function

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?

• ###### 4. Re: OBIEE Time Series Function

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.

• ###### 5. Re: OBIEE Time Series Function

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

• ###### 6. Re: OBIEE Time Series Function

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.

• ###### 7. Re: OBIEE Time Series Function

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.

• ###### 8. Re: OBIEE Time Series Function

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.

• ###### 9. Re: OBIEE Time Series Function

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.

• ###### 10. Re: OBIEE Time Series Function

Hi,

Thanks for the update.

Can you please provide some example or more explanation.

• ###### 11. Re: OBIEE Time Series Function

Hard to read your mind on what you don't understand but here goes; -

If you have a Total year measure then it must be based on either an ETL process populating an all year number, which does not seem likely, or a value that is aggregated and has its detail level set to the total level of your time hierarchical dimension.

Assuming it is the latter you need to copy it and have its copy set to the detail level of the time hierarchy.

Then you can create a new time series measure on this, that will use the AGO, YTD or whatever.

Then you will have the Year Total measure (as you do now), a detail equivalent that will only relate to the relevant time period, and a time series measure which will work, provided you have time in your analysis.

Finally you can then build a logical measure which makes the A - B functionality that you alluded to in your original question.

If this still does not make sense then please provide precise detail of what you are not understanding.

Note - I am saying to do all of the above on the RPD, business model layer (and presentation!)