topic Re: Set analysis in QlikView

Set analysis

Wed, 07 Jan 2015 08:10:48 GMT

Hi,

I want change the if condition to Set analysis and for the below expression I want change.

sum(if(SCENARIO='S1' or SCENARIO = 'JUMP-OFF' ,PD_FLOOR_WA*EAD_WA,0))/sum(if(SCENARIO='S1' or SCENARIO = 'JUMP-OFF' ,EAD_WA,0))*100

Thank you.

Re: Set analysis

alexandros17 — Wed, 07 Jan 2015 08:14:59 GMT

try this

sum({$ <SCENARIO={'S1',''JUMP-OFF'}>} PD_FLOOR_WA*EAD_WA)

sum({$ <SCENARIO={'S1',''JUMP-OFF'}>} EAD_WA)*100

Re: Set analysis

its_anandrjs — Wed, 07 Jan 2015 08:15:04 GMT

Hi,

Try this

sum( {<SCENARIO={'S1','JUMP-OFF'}>} PD_FLOOR_WA*EAD_WA )

sum( {<SCENARIO={'S1','JUMP-OFF'}>} EAD_WA )*100

Regards,

Anand

Re: Set analysis

alkesh_sharma — Wed, 07 Jan 2015 08:17:53 GMT

Try this:

((sum( {<SCENARIO={'S1'}>} PD_FLOOR_WA*EAD_WA )+

sum( {<SCENARIO={'JUMP-OFF'}>} PD_FLOOR_WA*EAD_WA ))

(sum( {<SCENARIO={'S1'}>} EAD_WA )+

sum( {<SCENARIO={'JUMP-OFF'}>} EAD_WA )))*100

Re: Set analysis

Colin-Albert — Wed, 07 Jan 2015 08:19:34 GMT

(Sum({$ <SCENARIO={'S1','JUMO-OFF'}>} PD_FLOOR_WA*EAD_WA)

Sum({$ <SCENARIO={'S1','JUMO-OFF'}>} PD_FLOOR_WA) ) *100

Re: Set analysis

Wed, 07 Jan 2015 08:36:39 GMT

Let me try

Re: Set analysis

Wed, 07 Jan 2015 10:38:12 GMT

Hi, With the above expressions, I could't get my output as expected and my outoupt should be as below.

Could you please suggest?

When selected:S1 my output should be as below, P0 should be disappear.

Period	Scenario =S1	Scenario =S2	Scenario =S3
P1	2%	-	-
P2	7%	-	-
P3	1%	-	-

When Jump off selected then my output should be like below, basically Jump-off=P0

Period	Scenario =S1	Scenario =S3	Scenario =S2
P0	4%	4%	4%

Thank you,

Re: Set analysis

rubenmarin — Wed, 07 Jan 2015 10:48:44 GMT

Hi Raj, I think you asking for intersection between selected values and set analysis, you can try:

sum( {<SCENARIO*={'S1','JUMP-OFF'}>} PD_FLOOR_WA*EAD_WA )

/ sum( {<SCENARIO*={'S1','JUMP-OFF'}>} EAD_WA )*100

Re: Set analysis

Wed, 07 Jan 2015 12:09:17 GMT

Working perfect Ruben, Thank you.

Re: Set analysis

Wed, 07 Jan 2015 12:18:57 GMT

Hi Ruben,

Is there any way for the below expression:

if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))

Thank you

Re: Set analysis

rubenmarin — Wed, 07 Jan 2015 12:24:07 GMT

Not sure if this will work:

Sum({<Trad_PRODUCT_TYPE ={'OTC Derivative'}>} Trad_UNREALIZED_PL)

+ Sum({<Trad_PRODUCT_TYPE ={'SFT'}>} Trad_COLLATERAL_VALUE)

+ Sum(Trad_CURR_BOOK_VALUE)

Also I'm not sure if this version has better performance than the 'if' version.

Re: Set analysis

Wed, 07 Jan 2015 12:52:47 GMT

Thanks Ruben,

I will try that...

Before that I want to know the functionality of the previous expression(

sum( {<SCENARIO*={'S1','JUMP-OFF'}>} PD_FLOOR_WA*EAD_WA )

/ sum( {<SCENARIO*={'S1','JUMP-OFF'}>} EAD_WA )*100)

what is the meaning of - SCENARIO*=

If possible, could you please expalin?

Thank you,

Re: Set analysis

rubenmarin — Wed, 07 Jan 2015 13:45:25 GMT

Hi,

SCENARIO --> Field you want to apply set analysis.

*= --> Means intersection, it is: filter the values selected and in the especified values