topic Re: Set analysis for IF in QlikView

Set analysis for IF

Fri, 26 Dec 2014 07:40:37 GMT

Hi,

Is there any alternate way for below IF condition? looking for set analysis.

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 for IF

alkesh_sharma — Fri, 26 Dec 2014 07:49:24 GMT

Try This,

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

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

SUM({<Trad_PRODUCT_TYPE -= {'SFT','OTC Derivative''}>}Trad_CURR_BOOK_VALUE)

Re: Set analysis for IF

Fri, 26 Dec 2014 07:51:20 GMT

try this

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

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

Sum({<Trad_PRODUCT_TYPE ='SFT'>} Trad_CURR_BOOK_VALUE)

Re: Set analysis for IF

amit_saini — Fri, 26 Dec 2014 07:57:30 GMT

Hi,

Try like this:

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

Sum({<Trad_PRODUCT_TYPE ={"SFT"}>} Trad_COLLATERAL_VALUE)

Sum({<Trad_PRODUCT_TYPE ={"SFT"}>} Trad_CURR_BOOK_VALUE)

Thanks,
AS

Re: Set analysis for IF

Fri, 26 Dec 2014 08:21:03 GMT

Hi,

Its not worked out with these set expression.

my selections are: AFS-HTM
DF Bank
OTC Derivative
PM Loan
SFT

Expre:

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 for IF

its_anandrjs — Fri, 26 Dec 2014 08:21:53 GMT

Try this way

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

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

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

Regards,

Aannd

Re: Set analysis for IF

amit_saini — Fri, 26 Dec 2014 08:26:27 GMT

Raj,

Please note that for Set analysis:

- The selection may be the current one ($ sign), the database (1), a bookmark or a group. The current

selection is the default selection, the $ may be omitted.

- There is no comma (,) between the set and the measure name

- The dimension or the measure name must be enclosed into brackets [] if the name contains specific

characters like a space, a hyphen - …

- Members are always enclosed into curly brackets {} : whatever the way to find them (by name, by

search string, by a function)

- Named members are separated with a comma (,)

- Numeric member names are not enclosed into single or double quotes

- Text members are enclosed into single or double quotes but not necessarily if they do not contain

specific characters like spaces

Ex: {<MANUFACTURER_LDESC = {"AMBOISE","BELLE"}, CATEGORY_LDESC={"ACC", "COLD PLATES

”}>}

Ex: {<MANUFACTURER_KEY = {253, 2789, 1200}, CATEGORY_LDESC={"ACC", "COLD PLATES”}>}

Thanks,
AS

Re: Set analysis for IF

jagan — Fri, 26 Dec 2014 08:38:36 GMT

Hi Raj,

The best approach is to handle this in script like below

TableName:

LOAD

if(Trad_PRODUCT_TYPE = 'OTC Derivative', Trad_UNREALIZED_PL,

if(Trad_PRODUCT_TYPE = 'SFT',Trad_COLLATERAL_VALUE,Trad_CURR_BOOK_VALUE)) AS CalcValue

FROM DataSource;

Now in charts you can straighaway use Sum(CalcValue)

Sum(Match(Pick(Match(Trad_PRODUCT_TYPE, 'OTC Derivative', 'SFT', '*'),

Trad_UNREALIZED_PL, Trad_COLLATERAL_VALUE, Trad_CURR_BOOK_VALUE))

Hope this helps you.

Regards,

Jagan.

Re: Set analysis for IF

Fri, 26 Dec 2014 09:05:21 GMT

Hi Jagan,

Chart option is not working out, let me try script part.

Thank you.

Re: Set analysis for IF

Sun, 28 Dec 2014 07:05:20 GMT

Hi Jagan,

Result is changing by keeping if statement part in script.

Thank you.

Re: Set analysis for IF

jagan — Mon, 29 Dec 2014 02:31:51 GMT

Hi Raj,

Script will work as expected, check the script and result again, if possible attach some same data.

Regards,

jagan.

Re: Set analysis for IF

Mon, 29 Dec 2014 07:21:36 GMT

Sure

Re: Set analysis for IF

Mon, 29 Dec 2014 09:22:07 GMT

Hi,

Please find the qvd file.

Thank you.