This discussion is archived
3 Replies Latest reply: May 8, 2012 9:26 AM by Satya Ranki Reddy RSS

Issue with nested aggregated in time series

935753 Newbie
Currently Being Moderated
Hi Experts,

I have a small requiremnt using time series.

eg : (sum(current year4:Quarters bookingamt)-sum(previous year4:Quarters bookingamt))/sum(previous year4:Quarters bookingamt)

Bookingamt is My measure Name.
how do we implement above logic can please help regarding this.



Thanks,
C Mahesh
  • 1. Re: Issue with nested aggregated in time series
    Diney Pro
    Currently Being Moderated
    In Answers we can do this calculation by using Filter function:

    (FILTER(Measure_Column using "Calendar".Year = Year(Current_Date))-FILTER(Measure_Column using "Calendar".Year = Year(Current_Date)-1))/FILTER(Measure_Column using "Calendar".Year = Year(Current_Date)-1)
  • 2. Re: Issue with nested aggregated in time series
    935753 Newbie
    Currently Being Moderated
    Thanks for your reply.

    But i want to do it only in RPD.


    Thanks,
    C Mahesh
  • 3. Re: Issue with nested aggregated in time series
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi Mahesh,

    These functions help to obtain the data analyzed based on the time. There are two types of Time Series in OBIEE viz..

    1) Ago
    2) To Date

    Following are the prerequisites and procedure to create time series function in OBIEE RPD.

    a) Create a dimensional Hierarchy (ex: YearàQTRàMonth) and convert it to the time dimension. DC on hierarchy and check the box time dimension.

    b) Define the Chronological Key. A chronological key should always be the lowest level in the table and the data in it should be in the format of 20090102 or 200109 (year, month format). DC on level (preferred/last—detail) go to KEYS tab and check chronological key.

    c) Duplicate the existing column, rename and specify the formula for the column.

    1) Ago: This function when specified in any column displays data that is month ago, 2 months ago depending on the specified formula. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series and then AGO. Then in metric go to logical tables and select metric and in level go to time dimension and select month (1 month ago) and give the period as 1(since it is 1 month ago) then transfer two new columns to presentation layer
    .
    Ex: sales: 100,200,300 à -, 100, 200 (1 month AGO).

    2) To Date: This functions allows us to display the aggregated measures based on the formula specified. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series then select TO DATE. Select metric from logical tables (revenue) and as level go to time dimension and select year(YTD) and OK.

    YTD- year to date – Displays aggregated values that is with respect to month since it is YTD. Similarly for QTD and MTD.

    Ex: sales: 100,200,300 à YTD Sales: 100,300,600

    http://www.rittmanmead.com/2007/04/obi-ee-time-dimensions-and-time-series-calculations/ -- How to implement step by step with screen shots.

    http://www.oraclebidwh.com/2009/12/time-series-wizard-in-obiee/ -- you can understand easily how to implement this with screen shot.

    Hope it help's

    Thanks,
    satya

Legend

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