Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (3)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: amount of events under variable time

This works in your example:

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

12 Replies

Re: amount of events under variable time

Try this and let me know

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

Not applicable

Re: amount of events under variable time

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

Could you please share your qvw?

MVP
MVP

Re: amount of events under variable time

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
Esteemed Contributor III

Re: amount of events under variable time

Maybe:

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

Not applicable

Re: amount of events under variable time

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

Re: amount of events under variable time

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

MVP
MVP

Re: amount of events under variable time

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
Esteemed Contributor III

Re: amount of events under variable time

This works in your example:

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