amount of events under variable time

[#1]

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

[alexandros17]

Try this and let me know

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

[#3]

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)

[Anonymous]

Maybe:

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

[#7]

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.

[#8]

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.

[Anonymous · Accepted solution]

This works in your example:

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