Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

amount of events under variable time

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

1 Solution

Accepted Solutions
mov
Champion III
Champion III

This works in your example:

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

View solution in original post

12 Replies
alexandros17

Try this and let me know

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

Not applicable
Author

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)

alexandros17

Could you please share your qvw?

jonathandienst

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mov
Champion III
Champion III

Maybe:

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

Not applicable
Author

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.

Not applicable
Author

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

jonathandienst

The expression I mentioned earlier

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

when loaded into your sample returns 4  as you require.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mov
Champion III
Champion III

This works in your example:

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

View solution in original post