set analysis expression

[souadouert]

HellO,

I have table with IN and flag dimensions , So in this expression  need to calculated  the value :

for example IND=10

flag =1 SO  IND= nominator /denominator -> = IND 20 / IND 30 -> = 10/15

[sasiparupudi1]

may be

if(flag=1,nominator/denominator,VALUE)

[souadouert]

nominator and denominator is the ref of inducateur and not the values

[rubenmarin]

Hi souad, maybe with:

Only({<IN={$(=Only({<flag={1}, IN={10}>} nominator))}>} VALUE)

/ Only({<IN={$(=Only({<flag={1}, IN={10}>} DENOMINATOR))}>} VALUE)

you can chage the fixed '10' to a variable or $-expansion expression to make it more dynamic

[souadouert]

nominateur is not the value , is the id of indicator

[sasiparupudi1]

It a bit confusing..are you saying that if the flag value is set then the next 2 below values of the indicator will become the numerator and denominators?

cos

20 and 30 are below 10  and 10 has flag set to 1

[rubenmarin]

nominator is used to get the IN, and the value of that IN, have you tried? It doesn't returns 10/15?

[Peter_Cammaert]

This is an old-fashioned example of Date Island use to create associations through expressions instead of through explicit links.

Add a Date Island table to your data model that consists of a copy of the IN & VALUE columns and rename the columns so that they link to nothing else. Let's say you call those copies DI_IN and DI_VALUE

Your expression may now look like:

=IF (flag = 1, Sum(IF (DI_IN = nominator, DI_VALUE))/Sum(IF (DI_IN = DENOMINATOR, DI_VALUE)), Sum(VALUE))

This will work on condition that

• the columns in your screenshot are actual internal table fields, and not just data in a QlikView UI object
• the IN and VALUE columns don't need aggregation/calculations of their own (Otherwise you'll have to precalculate their values in your script)
• you don't have tens of thousands of Indicators (using IF() in Aggr functions tends to be rather slow)

[jerem1234]

Another way in addition to what has been said could be linking your IN values in the load script to your nominator and denominator fields like:

Test:

Load * Inline [

IN, VALUE, nominator, DENOMINATOR, flag

10, 0, 20, 30, 1

20, 10, 10, 30, 0

30, 15, 10, 20, 0

40, 200, 20, 30, 0

];

Test2:

Load

IN as nominator,

VALUE as nominatorValue

RESIDENT Test;

Test3:

Load

IN as DENOMINATOR,

VALUE as DENOMINATORValue

RESIDENT Test;

And then use an expression like:

sum(nominatorValue)/sum(DENOMINATORValue)

Please find attached.

Hope this helps!