BusinessObjects Board

Variance calculation

building reports desktopintelligence
#1

Hi all,

We have a report with 2 date objects (format “DD-MM-YYYY HH:MM:SS”) and we want to create a new variable showing the difference between both dates. How can we define this variable?

Thanks,

Schuster :united_arab_emirates: (BOB member since 2002-08-29)

difference between time stamp
difference between two dates in hour minutes and seconds
Hours between two times?
Substracting dates
need formula to add minutes to date
How to display refresh Start datetime and end datetim in Des
Displaying elapsed time in decimal hours
Converting date to seconds
Subtracting Date and time
time between 2 times (duration)
Date manipulation
Seconds between dates
How to minus times objects
#2

Take a look at the BO Reporter function “DaysBetween”…

Andreas :de: (BOB member since 2002-06-20)

#3

From BO Help File

Syntax
DaysBetween(date 1; date 2)

Description
Returns the number of days between two dates.

Example
DaysBetween(‘10-04-94’;‘12-04-94’) returns 2.

Remark
To enter a constant after this function, you can either use the ToDate function, or place single quotes around the date (e.g., ‘04.04.96’).

avaksi :us: (BOB member since 2002-08-22)

#4

Hi, this works for dates, but I also need the difference between times. This means that I need to know the time difference (in hours for example) between two dates

In VB there is a function called DateDiff (DateDiff(DateInterval, Date1, Date2)) where you can say the time interval (DateInterval) you want to use as the unit of difference between Date1 and Date2.

Is there something similar in BO? If not, how can I get this value?

Schuster :united_arab_emirates: (BOB member since 2002-08-29)

#5

You might have to parse out the time portion of your date (hh:mm) then convert this value to a number by multiplying the hours * 60 + mm, do this for both dates and subtract the results from each other, then convert the result back to hh:mm.

Maybe, there is an easier way…

BO Reporter functions you might find helpful: FormatDate, SubStr, FormatNumber

It would be easier if you can do the calculation on the DB side and define an object in BO Designer (only works if the two Dates in question come from the same data provider).

Andreas :de: (BOB member since 2002-06-20)

#6

Agreed.

If you need to calculate a TimeBetween() result in BusinessObjects, the generally accepted way is to convert the entire date/time value into one unit (hours or seconds), do the math, and then conver the result back into the desired unit. Using FormatDate() you can convert the date value into a string. Using Substr() you can extract portions of the string. Using ToNumber() you can convert those string elements into numbers, and then convert the numbers into hours (minutes, seconds, whatever) to do the subtraction.

Convert the final result back into the desired time unit and you’re done. 8)

Here’s a sample:

=((( (ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 7, 2))) - (
(ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 7, 2)))) / 3600) +
(DaysBetween(<DATE1> ,<DATE2>)*24)

This converts two dates to their components, converts everything to seconds, subtracts, then converts back to hours for the final output. This is from an older “Variables” presentation that I did at a user conference… the entire presentation is online for review if you like.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

difference between the Max Date Time and Min Date Time
How to calculate the difference in 2 dates
Summing Time Durations
#7

See Here for mor on this.

Let me know your e-mail address if you want the dll.

ken.gaul :uk: (BOB member since 2002-06-18)

#8

Unfortunately, there’s no “TimeBetween” function, which would make like much easier. :nonod:

Unfortunately too, you can’t do simple math against dates and times like you can in Excel. :nonod:

The easiest solution would probably be to do all your date calculations in SQL, or at least convert your dates to numeric values and then do the date calcs in the report.

If that’s not an option, you can still convert the date/time to a numeric value and do your math from there. For example, starting with two dates “NowDate” and “TestDate”, you’d convert them to values as so:
NowValue=DaysBetween(ToDate(“01/01/1900 00:00:00” ,“mm/dd/yyyy HH:mm:ss”) ,) + (ToNumber(FormatDate( ,“HH”))/24) + (ToNumber(Right(FormatDate( ,“HHmm”) ,2) ) /1440) + (ToNumber(FormatDate( ,“ss”))/86400)

TestValue==DaysBetween(ToDate(“01/01/1900 00:00:00” ,“mm/dd/yyyy HH:mm:ss”) ,) + (ToNumber(FormatDate( ,“HH”))/24) + (ToNumber(Right(FormatDate( ,“HHmm”) ,2) ) /1440) + (ToNumber(FormatDate( ,“ss”))/86400)

The difference between these values represent days - i.e. a difference of 1.25 is one day, six hours.

Joe

joepeters :us: (BOB member since 2002-08-29)

#9

[quote:95744045f5=“ken.gaul”]See Here for mor on this.

Let me know your e-mail address if you want the dll.
[/quote]

Hi Ken,

Thanks, we already are in contact through the e-mail (I’ve received your dll this noon), but up to now, I don’t know how to interpret the result. I’ve sent to you another email showing some results

Schuster :united_arab_emirates: (BOB member since 2002-08-29)

#10

Got it.

datediff(bigger_Date,smaller_date) returns number of days to 5 decimal places.

Multiply up by the required figure

ken.gaul :uk: (BOB member since 2002-06-18)

#11

Thanks, both solutions works fine, Ken’s dll and Dave’s code.

Thanks again, :smiley:

Schuster :united_arab_emirates: (BOB member since 2002-08-29)

#12

Hopefully my dll is easier to code than daves code :wink: (Thats why I wrote it after all. I seemed to spend my time writing formats and mutiplying by mutiples of 60 for about 3 months, then I spend 1 week writing the dll. Think my priorities should have been altered sooner :mrgreen: )
Though I think the code will hadle situations much better where you get the dates round the wrong way. (mine doesn’t do negative differences :cry: )

ken.gaul :uk: (BOB member since 2002-06-18)

#13

[quote:38aebc1aad=“ken.gaul”]Though I think the code will hadle situations much better where you get the dates round the wrong way. (mine doesn’t do negative differences :cry: )
[/quote]

Yes, I believe that the code I wrote was tested for negative diffs… as when an actual date shipped happens before the promised date shipped, for example.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#14

[quote:7e8a1d679a=“ken.gaul”]Hopefully my dll is easier to code than daves code :wink: (Thats why I wrote it after all. I seemed to spend my time writing formats and mutiplying by mutiples of 60 for about 3 months, then I spend 1 week writing the dll. Think my priorities should have been altered sooner :mrgreen: )
Though I think the code will hadle situations much better where you get the dates round the wrong way. (mine doesn’t do negative differences :cry: )
[/quote]

Hi Ken,

Have you tested your dll (or custom functions in general) in version 6.0? I’ve tested this and I’ve seen that after upgrade to 6.0, these functions are not available. Do you know something about this?

Schuster :united_arab_emirates: (BOB member since 2002-08-29)

#15

Hi Dave

I am trying to implement the formulae…
that u have specified…its giving me a error…
any idea why???

Thanks in advance…

Neeraj

=((( (ToNumber(SubStr(FormatDate(<Reqst Time>,"HH:MM:SS") ,1 ,2))*60*60) + 
(ToNumber(SubStr(FormatDate(<Reqst Time> ,"HH:MM:SS"), 4, 2))* 60) + 
ToNumber(SubStr(FormatDate(<Reqst Time> ,"HH:MM:SS"), 7, 2))) - ( 
(ToNumber(SubStr(FormatDate(<Appr Time> ,"HH:MM:SS") ,1 ,2))*60*60) + 
(ToNumber(SubStr(FormatDate(<Appr Time,"HH:MM:SS"), 4, 2))* 60) + 
ToNumber(SubStr(FormatDate(<Appr Time>,"HH:MM:SS"), 7, 2)))) / 3600) + 
(DaysBetween(<Appr Time> ,<Reqst Time>)*24)

nupreti :india: (BOB member since 2005-04-28)

#16

What is the exact error message, please :confused:

Andreas :de: (BOB member since 2002-06-20)

#17

Incorrect data type…
The problem i think may be bec the object I have is a time stamp
and not a date… it only shows (hr:mm:ss)
and i have to subtract between two time stamps…

nupreti :india: (BOB member since 2005-04-28)

#18

Hi

I have a date also ie one object has date (dd/mm/yyyy) and the other has time stamp in terms (hhmmss)
what i tried 2 do before using ur formula to concatnate the two but then also its giving data typeDMB0003

Thanks
Neeraj
Jasson Report2.rep (60.0 KB)

nupreti :india: (BOB member since 2005-04-28)

#19

Hi Neeraj,

Have you found a solution of this problem?
I’ve a similar one:

I have 2 fields in date format but with time statements only. I need to calculate the difference the both times in hours/minutes but all functions applied on it return a #ERROR message.

exemple: FormatDate( ,“HH:MM:SS”) --> #ERROR

ChristopheA :fr: (BOB member since 2003-09-04)

#20

Hi Dave,

Where should I create this object? is this in designer or in Webi ( Report level variable) I tried in both places but its throwing me error.

Please help me with this.

Thanks,
SAM

sam4u :us: (BOB member since 2008-01-09)