SUM BETWEEN VALUES

[#1]

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

[stephencredmond]

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

[jagan]

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.

[#4]

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

[#5]

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

[stephencredmond]

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

[#7]

Using this:

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

it returns:

>= 2000 <= 25000

will paste into search box.

It returns just the top value.

[Sokkorn]

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