BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

[Webi 4.x] Join date and time into common column


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
okros1
Forum Member
Forum Member



Joined: 31 Jul 2020

Posts: 1



PostPosted: Fri Jul 31, 2020 3:13 am 
Post subject: Join date and time into common column

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
Back to top
Bill K
Principal Member
Principal Member



Joined: 24 Feb 2011

Posts: 136
Location: Massachusetts, USA



PostPosted: Tue Aug 04, 2020 2:38 pm 
Post subject: Re: Join date and time into common column

Hi, I'm not sure this is the best way, but...
Code:
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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0270 seconds using 18 queries. (SQL 0.0023 Parse 0.0008 Other 0.0239)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo