13 Replies Latest reply: Feb 8, 2016 11:30 AM by Riccardo Schillaci

# Formula Inside Set Analysis

Hi,

I want to use this expression:

=Only({<ATYPE={'VALUETYPE'},NUM_FROM={"<=1"},NUM_TO={">=1"}>} NUM_RESULT)

This is working fine, NUM_RESULT is the right value that I'm expecting from a between check (1 is between NUM_FROM and NUM_TO).

Now, I want to replace this 1 with a formula like this:

=avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC)

(I'm using this fomula in a pivot column, I can use also the column alias as field)

it works as a new pivot column, but If I replace the 1 with this, it doesn't work. I tried also with variables but it doesn't work.

I tried:

=Only({<ATYPE={'VALUETYPE'},NUM_FROM={"<=\$(vartest)"},NUM_TO={">=\$(vartest)"}>} NUM_RESULT)

where vartest is

avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC)

Thank you, best regards.

Riccardo

• ###### Re: Formula Inside Set Analysis

Do you have equal (=) before Avg in the variable? Try adding that and see if that helps?

• ###### Re: Formula Inside Set Analysis

Yes, I tried it.

It doesn't work for every lines.. It seems doesn't work at dimension-line.

Can I use a Calcolated Column ?

• ###### Re: Formula Inside Set Analysis

=Only( Aggr( If( Sum(VALUE) >= RANGE_FROM AND Sum(VALUE) <= RANGE_TO AND RANGE_TYPE = DIM_TYPE, RANGE_VALUE), DIMENSION, RANGE_TYPE, RANGE_VALUE))

• ###### Re: Formula Inside Set Analysis

And another approach using INTERVALMATCH in the script

```DIMFACT:
VALUE,
DIM_TYPE
FROM
[DimFact.xlsx]
(ooxml, embedded labels, table is DIMFACT);

RANGEVALUES:
RANGE_FROM,
RANGE_TO,
RANGE_VALUE
FROM
[RangeValues.xlsx]
(ooxml, embedded labels, table is VALUESRANGE);

JOIN
IntervalMatch(VALUE, DIM_TYPE)
RESIDENT RANGEVALUES;

LEFT JOIN (DIMFACT)
RESIDENT RANGEVALUES;

DROP TABLE RANGEVALUES;
```
• ###### Re: Formula Inside Set Analysis

What do you mean with 'doesn't work'?

Do you expect the average to be calculated per dimension line? Then this won't work indeed.

• ###### Re: Formula Inside Set Analysis

Yes, I would like that the average will be calculated at dimension line......... How can I do this?

Thank you, regards.

Riccardo

• ###### Re: Formula Inside Set Analysis

Set analysis is evaluated once per chart, not per dimension line, so you can't easily use set analysis here.

=Only(

{<ATYPE={'VALUETYPE'},NUM_FROM={"<=1"} >}

Aggr(

If( NUM_TO >= avg({<BTYPE={'ANOTHERTYPE'},YEAR=P(Year)>} VALUE_PERC), NUM_RESULT)

, YourChartDimension1, YourChartDimension2, ... , NUM_TO

)

)

Replace YourChartDimensionX with your chart dimension fields.

I am not 100% sure about the set expressions used in the inner and outer aggregation functions, so this may need to be adapted, too.

In general, it's easier to answer these questions knowing the data model and context,so if you still have issues,please post a small sample QVW.

• ###### Re: Formula Inside Set Analysis

Hi, thank you for reply. I'm going to check it and try it.... meanwhile I uploaded a little example.

Thank you, best regards.

Riccardo

• ###### Re: Formula Inside Set Analysis

May be try this expression:

Aggr(If(RANGE_TYPE = DIM_TYPE and VALUE >= RANGE_FROM and VALUE <= RANGE_TO, RANGE_VALUE), DIMENSION, RANGE_TO, RANGE_VALUE)

• ###### Re: Formula Inside Set Analysis

Hi! Thank you, it is correct and It works fine Thank you very much.

I have a second issue, similar logic but it uses the calculated new column for another range match. I uploaded the NEW app.... Can you check it for me please? Thank you very much

• ###### Re: Formula Inside Set Analysis

None of the Totals fall between the ranges for A or B. Am I reading this correct?

• ###### Re: Formula Inside Set Analysis

Yep, you are right. I uploaded the wrong file......... just change range_value where type is A and B

• ###### Re: Formula Inside Set Analysis

I tried changing the value for print A or B but it still doesn't work. It seems that I can't reuse column "TOTAL"