BusinessObjects Board

Converting a time (number) measure to Days/hours/mins/secs

building reports webintelligence
#1

I have two date/time stamps
1- order received dd/mm/yy hh;ss
2- order acknowledged dd/mm/yy hh;ss
these are turned into a measure using Datediff (seconds) from SQL server.

3- Received to Acknowledge
ss

I then want to convert this to show days/hours/mins/seconds
dd/hr/mi/se

I have previously been able to do days/hours/mins as per measure below.
dd/hr/mi/

Using measure below
=If( [Order Received to Acknowledged]<= 86400) Then
FormatNumber(Floor(Mod[Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins”
Else If ( [Order Received to Acknowledged] <= 172800) Then
FormatNumber(Floor([Order Received to Acknowledged] /86400) ;“0”) + " day, " +FormatNumber(Floor(Mod([Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins”
Else
FormatNumber(Floor([Order Received to Acknowledged] /86400) ;“0”) + " days, " +FormatNumber(Floor(Mod([Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins”

I have added a further argument for seconds which should logically be an extension of above

=If( [Order Received to Acknowledged] <= 86400) Then
FormatNumber(Floor(Mod([Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins” + FormatNumber(Floor(Mod(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;60) *60) ; “0”) + “secs”
Else If ( [Order Received to Acknowledged] <= 172800) Then
FormatNumber(Floor([Order Received to Acknowledged] /86400) ;“0”) + " day, " +FormatNumber(Floor(Mod([Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins”

  • FormatNumber(Floor(Mod(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;60)*60) ;“0”) + “secs”
    Else
    FormatNumber(Floor([Order Received to Acknowledged] /86400) ;“0”) + " days, " +FormatNumber(Floor(Mod([Order Received to Acknowledged] ;86400) /3600) ;“0”) + "hrs, " + FormatNumber(Floor(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;“0”) + “mins” + FormatNumber(Floor(Mod(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;60)*60 ;“0”) + “secs”

but webi tells me I have missed a “;” in my argument. I don’t think I have.

Would appreciate any help- or a better way of doing this

thanks :smiley:

scottsgoingon (BOB member since 2010-02-11)

#2

Compare this code with Marek’s code available here.

Jansi :india: (BOB member since 2008-05-12)

#3

Hi,
There is a syntax error in your formula and also with wrong calculation for seconds.
Syntax error:
FormatNumber(Floor(Mod(Mod(Mod([Order Received to Acknowledged] ;86400) ;3600)/60) ;60)*60 ;“0”) + “secs”
The First Mod syntax is wrong it doesn’t have second parameter seperated by ;.

Correct calculation for seconds:
FormatNumber(Floor(Mod(Mod(Mod([_Sec] ;86400) ;3600);60)) ; “0”)
Here is correct syntax:

=If( [_Sec] <= 86400) Then FormatNumber(Floor(Mod([_Sec] ;86400) /3600) ;"0") + "hrs, " + FormatNumber(Floor(Mod(Mod([_Sec] ;86400) ;3600)/60) ;"0") + "mins" + FormatNumber(Floor(Mod(Mod(Mod([_Sec] ;86400) ;3600);60)) ; "0") + "secs" Else If ( [_Sec] <= 172800) Then FormatNumber(Floor([_Sec] /86400) ;"0") + " day, " +FormatNumber(Floor(Mod([_Sec] ;86400) /3600) ;"0") + "hrs, " + FormatNumber(Floor(Mod(Mod([_Sec] ;86400) ;3600)/60) ;"0") + "mins" + FormatNumber(Floor(Mod(Mod(Mod([_Sec] ;86400) ;3600);60)) ; "0") + "secs" Else FormatNumber(Floor([_Sec] /86400) ;"0") + " days, " +FormatNumber(Floor(Mod([_Sec] ;86400) /3600) ;"0") + "hrs, " + FormatNumber(Floor(Mod(Mod([_Sec] ;86400) ;3600)/60) ;"0") + "mins" + FormatNumber(Floor(Mod(Mod(Mod([_Sec] ;86400) ;3600);60)) ; "0") + "secs"

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)