Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rschillaci
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
Datawarehouse & Business Intelligence
1 Solution

Accepted Solutions
jontydkpi
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
maxgro
Not applicable

Re: Set Analysis Compare Number with Range Classification

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

settu_periasamy
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


jontydkpi
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
rschillaci
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
Datawarehouse & Business Intelligence
vmoreno2605
Not applicable

Re: Set Analysis Compare Number with Range Classification

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

vmoreno2605
Not applicable

Re: Set Analysis Compare Number with Range Classification

2 years after*