Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
schillo87
New Contributor III

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

1 Solution

Accepted Solutions

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)

Capture.PNG

13 Replies

Re: Formula Inside Set Analysis

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

MVP
MVP

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.

schillo87
New Contributor III

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

schillo87
New Contributor III

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 ?

MVP
MVP

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.

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.

schillo87
New Contributor III

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)

Capture.PNG

MVP
MVP

Re: Formula Inside Set Analysis

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

MVP
MVP

Re: Formula Inside Set Analysis

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;

Community Browser