Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

SUM BETWEEN VALUES

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

7 Replies
Luminary
Luminary

Re: SUM BETWEEN VALUES

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

MVP & Luminary
MVP & Luminary

Re: SUM BETWEEN VALUES

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.

Not applicable

Re: SUM BETWEEN VALUES

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

Not applicable

Re: SUM BETWEEN VALUES

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

Luminary
Luminary

Re: SUM BETWEEN VALUES

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

Not applicable

Re: SUM BETWEEN VALUES

Using this:

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

it returns:

>= 2000 <= 25000

will paste into search box.

It returns just the top value.

Sokkorn
Honored Contributor

Re: SUM BETWEEN VALUES

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