topic Re: SUM BETWEEN VALUES in QlikView

SUM BETWEEN VALUES

Tue, 09 Jul 2013 18:38:03 GMT

Hello,

I want to sum all the values that are between two certain amounts.

i am using this expression:

= sum({<Top_Val= {">=$(=min(Low_Val))<=$(=max(Top_Val))"}> } Values)

any idea?

using that one gives me de last value that would appear by the Top_Val

Re: SUM BETWEEN VALUES

stephencredmond — Tue, 09 Jul 2013 19:32:13 GMT

Hi,

It should probably be something like:

sum({<Values= {">=$(=min(Low_Val))<=$(=max(Top_Val))"}> } Values)

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

Follow me on Twitter: @stephencredmond

Re: SUM BETWEEN VALUES

jagan — Tue, 09 Jul 2013 19:34:46 GMT

Create variables with

vMinValue = min(Low_Val)

vMaxValue = max(Top_Val)

Now use this variables in expression

= sum({<Values= {'>=$(=vMinValue )<=$(=vMaxValue )'}> } Values)

Hope this helps you.

REgards,

Jagan.

Re: SUM BETWEEN VALUES

Tue, 09 Jul 2013 19:39:05 GMT

Hi Thanks for your answer, but that way it returns '0'.

Re: SUM BETWEEN VALUES

Tue, 09 Jul 2013 19:39:50 GMT

Thank you, but that way it gives me the Top_Val again.

Re: SUM BETWEEN VALUES

stephencredmond — Tue, 09 Jul 2013 19:45:42 GMT

Hi,

The creation of variables is an additional step that is not needed.

The value will be 0 if the values that you have are not within the bounds of the min and max values.

Remember, that a set expression can be anything that you can type into a search expression in a List Box. If you add a list box for the Value field and then add a text object with the expression:

='>=$(=min(Low_Val))<=$(=max(Top_Val))'

First, does that return some text? If it does, paste that text into the search box of the List Box and see if it returns any values.

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

Follow me on Twitter: @stephencredmond

Re: SUM BETWEEN VALUES

Tue, 09 Jul 2013 19:52:23 GMT

Using this:

='>=$(=min(Low_Val))<=$(=max(Top_Val))'

it returns:

>= 2000 <= 25000

will paste into search box.

It returns just the top value.

Re: SUM BETWEEN VALUES

Sokkorn — Wed, 10 Jul 2013 03:42:25 GMT

Hi Sir,

Sum({$<[Values]={">=2000<=25000"}>} [Values]) -> This will sum [Values] where [Values] between 2000 and 25000.

Create variable like Mr. Jagan is the one of solution. But you can give a try with below solution also

=Sum({$<[Values] = {">=$(=Min({1} [Low_Val]))<=$(=Max({1} [Top_Val]))"} >} [Values])

Regards,

Sokkorn