topic Re: Set Analysis Compare Number with Range Classification in QlikView

Set Analysis Compare Number with Range Classification

Riccardo — Tue, 26 Jan 2016 17:20:46 GMT

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

Re: Set Analysis Compare Number with Range Classification

maxgro — Tue, 26 Jan 2016 19:29:56 GMT

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

Re: Set Analysis Compare Number with Range Classification

settu_periasamy — Wed, 27 Jan 2016 04:42:22 GMT

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

Re: Set Analysis Compare Number with Range Classification

jonathandienst — Wed, 27 Jan 2016 05:28:22 GMT

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.

Re: Set Analysis Compare Number with Range Classification

Riccardo — Mon, 01 Feb 2016 10:19:12 GMT

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))

Re: Set Analysis Compare Number with Range Classification

vmoreno2605 — Mon, 05 Nov 2018 18:50:24 GMT

Sorry i bother you 2 years before your answer xD

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

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

ADJ_HIRE_DATE={"=$(vFecha)-ADJ_HIRE_DATE<=90"}>}Distinct EMPLOYEE) -

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

ADJ_HIRE_DATE={"=$(vFecha)-ADJ_HIRE_DATE<=90"}>}Distinct EMPLOYEE) +

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

ADJ_HIRE_DATE={"=$(vFecha)-ADJ_HIRE_DATE<=90"}>}Distinct EMPLOYEE) -

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

ADJ_HIRE_DATE={"=$(vFecha)-ADJ_HIRE_DATE<=90"}>}Distinct EMPLOYEE)

That works, but i need to add something like >90 and <=180

if you read this, thanks! xd

Re: Set Analysis Compare Number with Range Classification

vmoreno2605 — Mon, 05 Nov 2018 18:50:48 GMT

2 years after*