expression less then predefined time

[#1]

hi,

I have 2 times a start time and an end time of an event. I want to build a gauge chart showing what percentage of the events takes no more then 10 minutes. For this i need an expression that counts all the events with "endtime-starttime<time(0:10)"

i have:

Count ({$<time(EndTime-StartTime) = {"<=$(=time(0:10))"}>} EventId)

Thanks for all the help!

[#2]

also 6,25%

[Anonymous]

Hello, Tom.

If you load you data like this, you can use Peter's idea:

Times_Temp:

LOAD EventId,

    Time as StartTime;

SQL SELECT *

FROM "Datebase".dbo.ActionTimes

WHERE Type = 'StartTime';

Left Join (Times_Temp)

LOAD EventId,

    Time as EndTime;

SQL SELECT *

FROM "Datebase".dbo.ActionTimes

WHERE Type = 'EndTime';

Times:

Load

    EventId,

    StartTime,

    EndTime,

    Minute(EndTime - StartTime) as DurationMinutes

Resident Times_Temp;

Drop Table Times_Temp;

Now you can try Peter's expressions:

= count ({<DurationMinutes = {"<=10"}>} EventId)/count(EventId)

or

= count({<DurationMinutes = {"<=$(vMinDur)"}>} EventId)/count(EventId)

[Peter_Cammaert]

One last correction: the minute()-based expression produces incorrect results. You'll only get the minute part of the result of subtracting StartTime from EndTime, thereby ignoring any days and hours (assuming some events will take that long)

Better expression is this:

Times:

LOAD

   :

   StartTime,

   EndTime,

   (date#(EndTime) - date#(StartTime))*1440 AS DurationMinutes

RESIDENT ...

This produces a correct number of minutes, including a fraction.

You may have to add date format specifiers to the date#() function calls.