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
scottsgoingon (BOB member since 2010-02-11)