# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Set Analysis Compare Number with Range Classification

Hi all,

I have a Pivot table with a dimension and N measures...

I want to match a Measure number "VALUE" with an EXCEL loaded with many rows as are the ranges with RANGE_FROM, RANGE_TO numbers.

I have a set analysis like this: sum({<VALUE={">=RANGE_FROM<=RANGE_TO"}>} RANGE_VALUE) but it doesn't work.

Can anyone help me?

Thank you very much, best regards.

Riccardo

Riccardo Schillaci
BI Technical Analyst
Tags (3)
1 Solution

Accepted Solutions
Not applicable

## Re: Set Analysis Compare Number with Range Classification

That sort of syntax will work with minor mods with variables, but I suspect you are attempting row by row comparisons. Set expressions are applied once, before the chart is constructed; they are not evaluated on a row by row basis.

If Range_From and Range_To are variables, then use:

=sum({<VALUE={">=\$(RANGE_FROM) <=\$(RANGE_TO)"}>} RANGE_VALUE)

If they are fields, then use sum(if())

=sum(If(VALUE >= RANGE_FROM and VALUE <=RANGE_TO, RANGE_VALUE))

or set a flag for these value in script and use the flag in your set expression.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
Not applicable

## Re: Set Analysis Compare Number with Range Classification

Could you post your script or a small .qvw with your problem?

Not applicable

## Re: Set Analysis Compare Number with Range Classification

Hi,

Do you want to compare the row by row based for your Range from and Range to?

Set Analysis will work on chart based not row based. May be try like

sum(If(VALUE>=RANGE_FROM and VALUE<=RANGE_TO, RANGE_VALUE))

set_analysis_intra-record.qvw

Not applicable

## Re: Set Analysis Compare Number with Range Classification

That sort of syntax will work with minor mods with variables, but I suspect you are attempting row by row comparisons. Set expressions are applied once, before the chart is constructed; they are not evaluated on a row by row basis.

If Range_From and Range_To are variables, then use:

=sum({<VALUE={">=\$(RANGE_FROM) <=\$(RANGE_TO)"}>} RANGE_VALUE)

If they are fields, then use sum(if())

=sum(If(VALUE >= RANGE_FROM and VALUE <=RANGE_TO, RANGE_VALUE))

or set a flag for these value in script and use the flag in your set expression.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

## Re: Set Analysis Compare Number with Range Classification

Hi all, thank you for your replies.

I'have two tables: A and B.

In A I've many rows with dimension and measures.

In B I've range start/end and its value.

I've to catch the value of table B when the measure from table A is between the range values.

This one doesn't work: =sum(If(VALUE >= RANGE_FROM and VALUE <=RANGE_TO, RANGE_VALUE))

Riccardo Schillaci
BI Technical Analyst
Not applicable

## Re: Set Analysis Compare Number with Range Classification

What if my range_from and range_to are not variables, they are numbers. this is my expression

Count({<FechaNum={"<\$(vFechaInicioAño)"},HireTerm={'H'},

Count({<FechaNum={"<\$(vFechaInicioAño)"},HireTerm={'T'},

Count({<FechaNum={"<=\$(vFecha)>=\$(vFechaInicioAño)"},HireTerm={'H'},

Count({<FechaNum={"<=\$(vFecha)>=\$(vFechaInicioAño)"},HireTerm={'T'},