topic amount of events under variable time in QlikView

amount of events under variable time

Wed, 03 Jun 2015 10:59:04 GMT

Dear community,

I have a problem in my dashboard I can't work out.

I have a couple of events from which I have a beginning and an end time. End time - begin time = duration of the event. All of this I load in my load script.

Now in my dashboard I want the user to insert a value into a variable $(DurationMinutes).

Now the part I can't work out:

I want a formula with which I calculate the number of events with a duration under or equal to $(DurationMinutes).

I tried this already:

=count ({<EventDuration = {"<=time(time#($(DurationMinutes),'m'), 'hh:mm:ss')"}>} EventDuration)

But this isn't working

When i change it in this:

=count ({<EventDuration = {"<=$(time(time#($(DurationMinutes),'m'), 'hh:mm:ss'))"}>} EventDuration)

It looks like he is counting all the events without taking selections in account (so selections won't work).

Can anyone help me with this please?

Thanks in advance!!

Re: amount of events under variable time

alexandros17 — Wed, 03 Jun 2015 11:01:03 GMT

Try this and let me know

count ({<EventDuration = {"<=$(=time(time#($(DurationMinutes),'m'), 'hh:mm:ss'))"}>} EventDuration)

Re: amount of events under variable time

Wed, 03 Jun 2015 11:08:54 GMT

Sorry my mistake, that was what i ment. You added an '=' right? That was also a sollution i already tried. It seems that QV counts all the events then and isn't taking any selection in account (so selections don't work)

Re: amount of events under variable time

alexandros17 — Wed, 03 Jun 2015 11:11:35 GMT

Could you please share your qvw?

Re: amount of events under variable time

jonathandienst — Wed, 03 Jun 2015 11:17:58 GMT

Is your duration simply he difference between two valid QV dates? Then it is a number in the units of decimal days. To convert the duration in minutes to decimal days would be by dividing the minutes by (60 * 24). So you would get this:

=Count({<EventDuration = {"<=$(=DurationMinutes / 60 / 24)"}>} EventDuration)

Re: amount of events under variable time

Anonymous — Wed, 03 Jun 2015 11:27:03 GMT

Maybe:

count(distinct if(EventDuration<=DurationMinutes, EventID))

Re: amount of events under variable time

Wed, 03 Jun 2015 11:28:00 GMT

The problem is that I work in an area in which I work with highly sensitive information. My customers can't effort there information to be out in the open. Sorry.

I made a test file for this problem. As you can see in the test file the textfield is giving back 6 instead of 4 that are under the 10 minutes.

Re: amount of events under variable time

Wed, 03 Jun 2015 11:30:55 GMT

This is a start! With this it gives back the amount under the DurationMinutes, But it isn't taking any selection in account

Re: amount of events under variable time

jonathandienst — Wed, 03 Jun 2015 11:34:03 GMT

The expression I mentioned earlier

=count ({<Duration = {"<=$(=DurationMinutes/60/24)"}>} Duration)

when loaded into your sample returns 4 as you require.

Re: amount of events under variable time

Anonymous — Wed, 03 Jun 2015 11:35:46 GMT

This works in your example:

=count(if(Duration<=DurationMinutes/1440, Duration))

Re: amount of events under variable time

Wed, 03 Jun 2015 11:36:37 GMT

i did see your reaction after posting thisone. I reacted to your reaction with the following: This is a start! With this it gives back the amount under the DurationMinutes, But it isn't taking any selection in account

Re: amount of events under variable time

Wed, 03 Jun 2015 11:38:53 GMT

but not when you select 3 times in Duration. Then the 4 will stay a 4 instead of adapting to the selection

Re: amount of events under variable time

Wed, 03 Jun 2015 11:48:25 GMT

sorry This is the right answer! My apologies for that, I read it wrong.

Thanks for your help!