3 Replies Latest reply: May 8, 2012 11:26 AM by Satya Ranki Reddy RSS

    Issue with nested aggregated in time series

    935753
      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
          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
            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
              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