topic Re: set analysis expression in QlikView

set analysis expression

souadouert — Fri, 04 May 2018 10:01:07 GMT

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

Re: set analysis expression

sasiparupudi1 — Fri, 04 May 2018 10:09:27 GMT

may be

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

Re: set analysis expression

souadouert — Fri, 04 May 2018 10:15:30 GMT

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

Re: set analysis expression

rubenmarin — Fri, 04 May 2018 10:19:41 GMT

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

Re: set analysis expression

souadouert — Fri, 04 May 2018 10:54:02 GMT

nominateur is not the value , is the id of indicator

Re: set analysis expression

sasiparupudi1 — Fri, 04 May 2018 11:28:06 GMT

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

Re: set analysis expression

rubenmarin — Fri, 04 May 2018 11:31:50 GMT

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

Re: set analysis expression

Peter_Cammaert — Fri, 04 May 2018 11:50:03 GMT

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)

Re: set analysis expression

jerem1234 — Fri, 04 May 2018 17:13:25 GMT

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!