BusinessObjects Board

Join date and time into common column

building reports webintelligence
#1

Dear Experts.

I am a beginner user and I need some help. I have 2 dimension: 1 date and 1 time. I would like to join them into 1 datetime column in order to subtract the result from another datetime. The purpose is to get the difference between the datetimes in hours and mins:

(7/31/2020 + 22:00:00) - (8/01/2020 + 06:00:00) = 8:00:00

I tried to concatenate them but off course it is not working, because the result is string and I am not able to count with strings.
The date dimension’s format is datetime, but if I click on the time dimension the format menu becomes inactive. I tried to create variants with toDate and formatDate functions but I don’t have enough experience.

Could you please help? Maybe you need more detail just write me.
Thank you very much.
okros1

okros1 (BOB member since 2020-07-31)

#2

Hi, I’m not sure this is the best way, but…

dt1 = ToDate( FormatDate( [d1]; "yyyyMMdd") + FormatDate( [t1]; "hhmmss"); "yyyyMMddhhmmss")
dt2 = ToDate( FormatDate( [d2]; "yyyyMMdd") + FormatDate( [t2]; "hhmmss"); "yyyyMMddhhmmss")
hdiff = TimeBetween( [dt1]; [dt2]; HourPeriod)

Where d1 is your first date, t1 is your first time, dt1 is the combined date and time 1, then d2, t2, and dt2 are the similar values for the second time point. hdiff is the difference between them in hours. You could also use MinutePeriod, SecondPeriod, DayPeriod, or a few others.

Bill K (BOB member since 2011-02-24)