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]

try rather something like this:

count({<EventId={"=minute(EndTime-StartTime)<=10"}>}EventId)

regards

Darek

[#3]

Thanks. The chart is at least showing something. The problem is that it isn't workin properly.

I made a selection of 3 date time values see below:

EventId	StartTime	EndTime
1	18-08-2011 20:35:25	18-8-2011 21:11:53
2	28-10-2012 10:44:21	28-10-2012 10:56:36
3	31-8-2013 20:47:20	31-8-2013 20:49:27

that means the minutes are:

EventId	minute(EndTime-StartTime)
1	36
2	12
3	2

so the gauge chart should show 33,33...% but it is showing 100%

any idea's?

[#4]

maybe:

count({<EventId={"=minute(EndTime-StartTime)<=10"}>}EventId)/count(EventId)

[#5]

that gives a value of 6,25%

[Peter_Cammaert · Accepted solution]

Can you add the difference between EndTime and StartTime as a column to your Events table? Its value should equal the expression your already applied, e.g. minute(EndTime-StartTime) AS DurationMinutes

Then your gauge expression becomes:

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

or if you want to make the minimum duration fexible, you can add a slider to set a variable called vMinDur, and your expression becomes:

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

[#7]

i think this 6,25 is not 33,33...% becouse "count(EventId)" is counting all EventId's not only the ones i have selected right?

[Peter_Cammaert]

IMHO this won't work, as a set is determined before the resulting object is calculated on the set result.

That means that you try to stuff a lot of data in a single =minutes() expression.

[#9]

try:

count({<EventId={"=minute(EndTime-StartTime)<=10"}>}distinct EventId)/count(distinct EventId)

[#10]

i tried. Problem is that in the database all the records are its saved like this:

EventId	Type	Time
1	StartTime	18-08-2011 20:35:25
2	StartTime	28-10-2012 10:44:21
2	EndTime	28-10-2012 10:56:36
3	StartTime	31-8-2013 20:47:20
1	EndTime	18-8-2011 21:11:53
3	EndTime	31-8-2013 20:49:27

They are saved with a type and a time.

My load script is like this now:

Times:

LOAD EventId,

    Time as StartTime;

SQL SELECT *

FROM "Datebase".dbo.ActionTimes

WHERE Type = 'StartTime';

Times:

LOAD EventId,

    Time as EndTime;

SQL SELECT *

FROM "Datebase".dbo.ActionTimes

WHERE Type = 'EndTime';

If you have any idea's...