Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Displaying a Date row 1 on row 2 in BI answers
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.
| Event | Start date | End Date | End Date previous event |
|---|---|---|---|
| Event 1 | 01-01-2017 | 30-01-2017 | - |
| Event 2 | 01-02-2017 | 27-02-2017 | 30-01-2017 |
| Event 3 | 01-05-2017 | 30-05-2017 | 27-02-2017 |
| Event 4 | 01-06-2017 | 30-06-2017 | 30-05-2017 |
Thanks in advance!
Answers
-
Looks like you need to use something like a LEAD/ LAG database function to get the data.
0 -
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.
0 -
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)
0 -
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
0 -
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
0 -
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
0
