Displaying a Date row 1 on row 2 in BI answers — Oracle Analytics

Oracle Analytics Cloud and Server

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

Displaying a Date row 1 on row 2 in BI answers

Received Response
41
Views
6
Comments
3028962
3028962 Rank 1 - Community Starter

Hi,

I am having a little difficulty displaying some data and I am hoping someone could give me a nudge in the right direction.

I need to know if the start date of the new event is less than 2 weeks apart of the previous event. Therefore I would like to know how I could show the end date of the previous event on the current row.

EventStart dateEnd DateEnd Date previous event
Event 101-01-201730-01-2017-
Event 201-02-201727-02-201730-01-2017
Event 301-05-201730-05-201727-02-2017
Event 401-06-201730-06-201730-05-2017

Thanks in advance!

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Looks like you need to use something like a LEAD/ LAG database function to get the data.

  • If you think from a modelling point of view you easily see that you don't have anything letting you get that value so far.

    So an option is what Sherry George posted: if on Oracle database by using EVALUATE you can get that info.

    But in general your analysis isn't an analysis itself ... You don't have measures and your model isn't able to answer your needs, so depending on your context and environment you maybe want to think at different options: if just displaying that thing can also be retrieved by a opaque view mapped in the physical layer > BMM > presentation layer and in the view you add the logic with LEAD/LAG.

    Ideally you make that at the ETL level: adding a reference to link back to previous line or directly by retrieving the date etc.

  • 3365160
    3365160 Rank 3 - Community Apprentice

    0. Uses the Oracle Lead function

       Ex: lead(end_date) over (partition by event order by end_date) as end_date_previous_event,

    1. create a function that will retrieve the end date of each previous event

    2. creates a flag in the BMM, which will call this function with as parameter (end_date)

       To invoke a custom function in OBIEE, use the function "EVALUATE"

    4. Finally you will just have to drag each of your fields

        (Event, start_date, end_date, end_date_previous_event)

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Just in case if you have EndDate Row_WID or  EndDate in number format like  DDMMYYYY or YYYYMMDD format then try this and later you may format it back to date format.

    The below is works for number datatypes only.

    msum(EndDate ,2)-EndDate

  • SPowell42
    SPowell42 Rank 5 - Community Champion

    I suspect you're going to need to update your data model...the requirement sounds simple enough "did the previous event happen within 2 weeks"...but these things are maddeningly difficult to code for. For instance. do you include weekends? Holidays? Leap year vs. non-leap year?

    Time based calcs are are always a pain. You might need to design a specialized calendar, depending on how easy or complex the underlying business logic is around the questions (if it's simply "did it happen within the last 14 days, don't care about weekends / holidays / leap years then the lag function should work).

    Hope this helps,

    Scott

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    if you are still looking for solution then, try this. Just putting it simple once you get the data then you need to format the data as ##-##-#### using Data Format

    in short : msum(EndDate ,2)-EndDate

    in detail:

    MSUM((cast(replace((Cast(DayofMonth(EndDate) as char)||cast(Month(EndDate)  as char)||cast(Year(EndDate)  as char)),' ','') as int)),2) -(cast(replace((Cast(DayofMonth(EndDate) as char)||cast(Month(EndDate)  as char)||cast(Year(EndDate)  as char)),' ','') as int))

    Based on your date values you may use substring instead of date functions for year, month and DayofMonth