Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Riccardo
Partner - Creator III
Partner - Creator 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

Riccardo Schillaci
BI Analyst
Datawarehouse & Business Intelligence
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

13 Replies
sunny_talwar

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

swuehl
MVP
MVP

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.

Riccardo
Partner - Creator III
Partner - Creator III
Author

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

Thank you, regards.

Riccardo

Riccardo Schillaci
BI Analyst
Datawarehouse & Business Intelligence
Riccardo
Partner - Creator III
Partner - Creator III
Author

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 ?

Riccardo Schillaci
BI Analyst
Datawarehouse & Business Intelligence
swuehl
MVP
MVP

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.

Riccardo
Partner - Creator III
Partner - Creator III
Author

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

Riccardo Schillaci
BI Analyst
Datawarehouse & Business Intelligence
sunny_talwar

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

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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;