topic Set Analyses Exclude Selection in QlikView

Set Analyses Exclude Selection

qlikconsult — Mon, 24 Mar 2014 05:50:16 GMT

I've got a Chart Type Straight table with the expression:

sum({<Source={'SALES'}>} NrOfCartons)

Then I have a list box with the field [Consignee].

How would I write the expression to exclude the the selection(s) made in the [Consignee] field?

Will really appreciate it if someone can give me a hand

Re: Set Analyses Exclude Selection

Mon, 24 Mar 2014 05:54:30 GMT

Can you please try below:

sum({<Source={'SALES'},Consignee=>} NrOfCartons)

Re: Set Analyses Exclude Selection

jagan — Mon, 24 Mar 2014 05:56:40 GMT

Hi,

Use Consignee= in set analysis expression, it ignores the selection in the field.

=sum({<Source={'SALES'},Consignee=>} NrOfCartons)

Regards,

Jagan.

Re: Set Analyses Exclude Selection

qlikconsult — Mon, 24 Mar 2014 06:12:21 GMT

Thanks guys, tried it, but not working yet:

Re: Set Analyses Exclude Selection

PrashantSangle — Mon, 24 Mar 2014 06:21:50 GMT

Hi,

You can try this,

=sum({<Source={'SALES'},Consignee=e(Consignee)>} NrOfCartons)

Regards,

Re: Set Analyses Exclude Selection

sushil353 — Mon, 24 Mar 2014 06:37:43 GMT

Hi,

Try this:

=sum({1-$<Consignee,Source={'SALES'}>} NrOfCartons)

HTH

Sushil

Re: Re: Set Analyses Exclude Selection

Mon, 24 Mar 2014 07:10:56 GMT

Try as attached:

1) You have to keep the Consignee= in the set anlaysis of expression.

2) Then you need to edit the dimension of Consignee in the pivot/straight table to use below expression

=if(not Match(Consignee,$(vConsignee)),Consignee)

check the Suppress the NULL value checkbox.

3) Create a variable named : vConsignee as below

=replace(chr(39) & GetFieldSelections(Consignee)& chr(39),', ',(chr(39)&', '&chr(39)))

Hope this was helpful.

Thanks,

Angad

Re: Set Analyses Exclude Selection

MK_QSL — Mon, 24 Mar 2014 07:32:36 GMT

IF(NOT IsNull(GetFieldSelections(Consignee)),SUM({<Source={'SALES'},Consignee= E(Consignee)>}NrOfCartons),SUM({<Source={'SALES'}>}NrOfCartons))

Re: Set Analyses Exclude Selection

semperaugustus — Mon, 24 Mar 2014 07:53:55 GMT

I think this is the right way.

There is also another solution:

1) add a new Alternate State and call it "Consignee".

2) apply this alternate state only to the list box

3) insert in the chart the expression: sum({$-[Consignee]<Source={'SALES'}>} NrOfCartoons)

Re: Set Analyses Exclude Selection

ashwanin — Mon, 31 Mar 2014 12:20:28 GMT

Hi,

Please use

sum({1<Source={'SALES'}>} NrOfCartons)