четверг, 7 ноября 2013 г.

SAP BO Web Intelligence Time difference in seconds between two dates

I got easy task, to calculate time in seconds between two dates. However, SAP BO provide only one function only related to dates - DaysBetween.

There is a example of report:
If you deal with this task, you should do workaround, of course with DatesBetween.
At first for each block we need to define Min and Max:

Min Time=Min([Query 1].[Datetime]) In ([Query 1].[Uniqueid])
Max Time=Max([Query 1].[Datetime]) In ([Query 1].[Uniqueid])

Than create formula and find diff between Max and Min time:

=DaysBetween([Min Time];[Max Time]) * 86400+(ToNumber(FormatDate([Max Time] ;"HH")) * 3600 +ToNumber(Left(FormatDate([Max Time];"mm:ss") ;2)) * 60 + ToNumber(FormatDate([Max Time];"ss"))) - (ToNumber(FormatDate([Min Time];"HH")) * 3600 +ToNumber(Left(FormatDate([Min Time];"mm:ss") ;2)) * 60 + ToNumber(FormatDate([Min Time];"ss")))

There is result: