Set Analysis Compare Number with Range Classification

[Riccardo]

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

[maxgro]

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

[settu_periasamy]

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

[jonathandienst · Accepted solution]

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.

[Riccardo]

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

[vmoreno2605]

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]

2 years after*