Displaying a Date row 1 on row 2 in BI answers

Question

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 eventEvent 101-01-201730-01-2017-Event 201-02-201727-02-201730-01-2017Event 301-05-201730-05-201727-02-2017Event 401-06-201730-06-201730-05-2017

Thanks in advance!

SriniVEERAVALLI · Answer

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

SPowell42 · Answer

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 · Answer

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