topic Re: Re: expression less then predefined time in QlikView

expression less then predefined time

Thu, 05 Jun 2014 10:02:33 GMT

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!

Re: expression less then predefined time

Thu, 05 Jun 2014 10:23:09 GMT

try rather something like this:

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

regards

Darek

Re: Re: expression less then predefined time

Thu, 05 Jun 2014 11:18:46 GMT

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?

Re: Re: expression less then predefined time

Thu, 05 Jun 2014 11:21:10 GMT

maybe:

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

Re: expression less then predefined time

Thu, 05 Jun 2014 11:24:44 GMT

that gives a value of 6,25%

Re: expression less then predefined time

Peter_Cammaert — Thu, 05 Jun 2014 11:27:17 GMT

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)

Re: expression less then predefined time

Thu, 05 Jun 2014 11:27:43 GMT

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?

Re: expression less then predefined time

Peter_Cammaert — Thu, 05 Jun 2014 11:32:54 GMT

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.

Re: expression less then predefined time

Thu, 05 Jun 2014 11:33:56 GMT

try:

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

Re: Re: expression less then predefined time

Thu, 05 Jun 2014 11:35:09 GMT

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...

Re: Re: expression less then predefined time

Thu, 05 Jun 2014 11:47:41 GMT

also 6,25%

Re: expression less then predefined time

Anonymous — Thu, 05 Jun 2014 11:54:42 GMT

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)

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

Re: expression less then predefined time

Peter_Cammaert — Thu, 05 Jun 2014 12:07:53 GMT

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.