topic Re: Values greater than variable1 and smaller than variable2 in QlikView

Values greater than variable1 and smaller than variable2

Janneke — Tue, 05 Sep 2017 14:03:35 GMT

Hi all,

I have an application in QlikView with a table that contains values depending among others on two variables.

The expression is as follows:

=Sum({$<Amount={'>=$(=vFilterLow)<$(=vFilterHigh)'}>}Value)

To set vFilterLow and vFilterHigh I use an input box.

I can change the variables and the table reacts as expected. Only, when I put in rubbish like vFilterLow=1000 and vFilterHigh=0 I expect zero values in the table (since none of the amounts is greater than 1000 and smaller than 0). However, the table does contain rows.

What is happening here?

I hope you can help me. Thanks in advance.

Janneke.

Re: Values greater than variable1 and smaller than variable2

vishsaggi — Tue, 05 Sep 2017 14:07:52 GMT

Is your expression working with different set of values entered? If yes, then can you share your sample to look into please?

Re: Values greater than variable1 and smaller than variable2

Janneke — Tue, 05 Sep 2017 14:31:22 GMT

Hi,

This is an example of my problem.

I hope this makes it easier to understand the issue.

Thank you all for helping.

Janneke.

Re: Values greater than variable1 and smaller than variable2

Peter_Cammaert — Tue, 05 Sep 2017 14:58:33 GMT

Hmmm, I get this:

This is not what you expect?

Re: Values greater than variable1 and smaller than variable2

vishsaggi — Tue, 05 Sep 2017 14:58:36 GMT

IS this not what you are getting?

Re: Values greater than variable1 and smaller than variable2

Janneke — Tue, 05 Sep 2017 15:10:36 GMT

Hi Vishwarath,

If you 'play' with the input variables, you'll see that sometimes it shows the right table, but also it can show values.

For instance, if I immediately put in 1000 and 0 I get your screenshot. If I then change the 0 to 200 I get this:

And this is also an outcome:

It doesn't always show what I expect....

Re: Values greater than variable1 and smaller than variable2

Janneke — Tue, 05 Sep 2017 15:20:09 GMT

Hi Peter,

Please check the answer I gave to Vishwarath.

Yes, your screenshot is what I expect with these values. In the provided sample I simplified my application, I guess that's why the outcome is different than my original application .

If you 'play' with the variables you see unexpected outcomes (at least unexpected for me), such as shown in my reply to Vishwarath.

Do you have any suggestions on how to fix this?

Janneke.

Re: Values greater than variable1 and smaller than variable2

Peter_Cammaert — Tue, 05 Sep 2017 15:36:12 GMT

Yes, of course there is a fix for this. It's still pretty ridiculous to let users specify amount restrictions like

1000 < Amount < 50

so we should add a couple of constraints to the From: and To: values. Just like what is commonly done when using variables to specify calendar periods (e.g. From should come before Until and vice versa)

For each input variable, add a Custom constraint on the Constraints tab to force input values

for From to be smaller than or equal to To
for To to be larger than or equal to From

Also add default values for each variable, optionally based on the minimum and maximum values of the field you try to filter (like =Min({1}Amount) and =Max({1}Amount) )

Best,

Peter

Re: Values greater than variable1 and smaller than variable2

Janneke — Fri, 08 Sep 2017 12:08:54 GMT

Thank you Peter,

I have put in $<=vFilterHigh and $>=vFilterLow and everything works great.

Thanks for the help!

Greetings, Janneke.

Re: Values greater than variable1 and smaller than variable2

KishoreSuri — Mon, 08 Jul 2019 12:22:17 GMT

Please share the expression and details how to got the values of Min and Max and How to be enter between this range.