BusinessObjects Board

Calculating difference between times..

building reports webintelligence
#1

I have a universe that gives me [date] and [time] of event in two separate details… & yes I would like them together in one for obvious reasons - but I’m not going to get that immediately.

I need to calculate the time difference between X & Y. I can create a variable that gives me :

=([Job Event Date]+" "+[Job Event Time]) Where ([Job Event]=“ENTERED”) In ([Job ID])

and

=([Job Event Date]+" "+[Job Event Time]) Where ([Job Event]=“PRACTICALLY COMPLETE”) In ([Job ID])

Those are my two variables that get me start and end dates of my jobs. There are multiple [Job Events] per job hence the In [Job ID] where clause so I just one instance per row in a simple table of job id, start and end date.

I’ve come stuck, becasue XI isn’t treating my variables as dates. I’ve tried wrapping them in a todate() statement but that just brings up errors.

Any ideas (other than universe development of creating two seemingly standard variables giving me what I need immediately.)?

Also, once I’ve got my date & times sorted - how would you go about calculating the time difference between them in BO? Can it do [end]-[start] to give me a numeric value as I’d do in Excel?

Many thanks for your help in advance.

Steve D

SteveD :uk: (BOB member since 2009-11-02)

Date difference
#2

Do you know what…?

Sometimes on a Friday I get blond moments! The reason that I couldn’t wrap my statements in the todate() function to get BO to treat them as dates & times was that our universes time variable only returns hh:mm not hh:mm:ss… :hb:

I tried this when I got in this morning:

=ToDate(([Job Event Date]+" “+[Job Event Time]+”:00") Where ([Job Event]=“ENTERED”) In ([Job ID]);“dd/mm/yyyy HH:mm:ss”)

and it works fine. I do still have an issue because webi XI doesn’t seem to have a datediff function because I want to get to the number of seconds between my start and end dates but I’m closing this topic now, and am going to have a search on the forum before posting a question about that - I may not need it if the resource is here already. I have done this previously in deski by converting the date to a number and I forgot to bring that stuff in with me today! :nopity:

SteveD :uk: (BOB member since 2009-11-02)

#3

For anyone interested - or that stumbles on this topic. The answer lies here courtesy of Marek Chladny

The formulas there in the topic are for DeskI so you will need to modify them for WebI because WebI has slightly different syntax of some functions. Since this is the Webi forum here are the Webi versions to give you:

Number of seconds:
=DaysBetween([Entered Time] ;[Completed Time]) * 86400
+
(
ToNumber(FormatDate([Completed Time];“HH”)) * 3600 +
ToNumber(Left(FormatDate([Completed Time] ;“mm:ss”) ;2)) * 60 +
ToNumber(FormatDate([Completed Time] ;“ss”))
)

(
ToNumber(FormatDate([Entered Time] ;“HH”)) * 3600 +
ToNumber(Left(FormatDate([Entered Time] ;“mm:ss”) ;2)) * 60 +
ToNumber(FormatDate([Entered Time] ;“ss”))
)

which is all I really need but the following code demonstrates how you can format it into a more digestible format - depends on your needs I guess.

=FormatNumber(Floor([Number of Seconds]/86400) ;“0”) + " day(s) " + FormatNumber(Floor(Mod([Number of Seconds] ;86400)/3600) ;“00”) + “:” + FormatNumber(Floor(Mod(Mod([Number of Seconds] ;86400) ;3600)/60) ;“00”) + “:” + FormatNumber(Mod(Mod(Mod([Number of Seconds] ;86400) ;3600) ;60) ;“00”)

Again, kudos to Marek for this. I could have spent all morning figuring this back out - years since I’ve done it!

SteveD :uk: (BOB member since 2009-11-02)

#4

Thanks 8) I am really glad that you found my post useful :yesnod:

Marek Chladny :slovakia: (BOB member since 2003-11-27)