BusinessObjects Board

Calculate Time difference between two date/time fields

I have found many calculations to calculate time difference (minutes) between two date/time fields. However I have not been able to perform aggregate functions (sum, average,etc.) on those fields even though they are measures. I get #MULTIVALUE error. :confused:


StuartDoss (BOB member since 2003-03-03)

can you define it more clearly or
attach a sample report


arvind.alex :uk: (BOB member since 2005-05-14)

It will be easy for us if you give us more information or by simple example of what you are trying…Anyhow try this…

Try by taking “Max or Min” functions and see…

Srinath


srinath2003 (BOB member since 2005-01-06)

I’m going to hazard a guess - your two measures are coming from different data providers?

If so, you may have more values in one data cube for the dimensions in your data block than you have in the other.

Try using the report function SUM(measure1) around each. If you still have trouble, try also using “magic” function for multiple data providers – Multicube? It just might make the difference: SUM(Multicube())


Anita Craig :us: (BOB member since 2002-06-17)

I create 3 layers of fields to create difference in time.
1st I create a Days_into_Minutes field
=(DaysBetween( ,)2460)

2nd I create Start_Minutes field
= If IsNull() Then 0.00 Else ToNumber(FormatDate( ,“HH”))*60+(ToNumber(SubStr(FormatDate ,“HH:mm”) ,4 ,2)))

3rd I create End_Minutes field
= If IsNull(<Start Date/Time>) Then 0.00 Else ((ToNumber(FormatDate( ,“HH”))*60)+(ToNumber(SubStr(FormatDate<Start Date/Time>,“HH:mm”) ,4 ,2))))+<Days_into_Minutes (New-ICC)>

4th I create Minutes_Difference field
=-


StuartDoss (BOB member since 2003-03-03)

or to make life a little easier you could try using the external library functions available from here
and just use =datediff(,)2460
(Unless of course you are on XI in which case YMMV)


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