topic Re: Formula Inside Set Analysis in QlikView

Formula Inside Set Analysis

Riccardo — Wed, 03 Feb 2016 09:58:47 GMT

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

sunny_talwar — Wed, 03 Feb 2016 17:42:30 GMT

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

Re: Formula Inside Set Analysis

swuehl — Wed, 03 Feb 2016 17:50:12 GMT

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

Riccardo — Mon, 08 Feb 2016 08:16:57 GMT

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

Riccardo — Mon, 08 Feb 2016 10:08:34 GMT

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

swuehl — Mon, 08 Feb 2016 11:38:40 GMT

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

Start with advanced aggregation, something like

=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

Riccardo — Mon, 08 Feb 2016 12:04:00 GMT

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

sunny_talwar — Mon, 08 Feb 2016 13:27:26 GMT

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

swuehl — Mon, 08 Feb 2016 13:29:50 GMT

You can start with an expression like

=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

swuehl — Mon, 08 Feb 2016 13:51:21 GMT

And another approach using INTERVALMATCH in the script

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


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


JOIN
IntervalMatch(VALUE, DIM_TYPE)
LOAD RANGE_FROM, RANGE_TO, DIM_TYPE
RESIDENT RANGEVALUES;


LEFT JOIN (DIMFACT)
LOAD DIM_TYPE, VALUE, RANGE_VALUE
RESIDENT RANGEVALUES;


DROP TABLE RANGEVALUES;

Re: Formula Inside Set Analysis

Riccardo — Mon, 08 Feb 2016 14:21:45 GMT

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

sunny_talwar — Mon, 08 Feb 2016 16:27:29 GMT

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

Re: Formula Inside Set Analysis

Riccardo — Mon, 08 Feb 2016 16:30:08 GMT

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

Re: Formula Inside Set Analysis

Riccardo — Mon, 08 Feb 2016 16:32:01 GMT

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"