topic Re: Set Analysis - Exclude reason code in QlikView

Set Analysis - Exclude reason code

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm new to QV and need help with an expression.

I need to count all the unique order numbers based on the current selection and exclude all cancelled orders.

The order number is stored in the field [%OrderNo]. [CloseReason] holds the cancelled order flag 'CNX'.

I am currently counting unique orders using:

=Count(DISTINCT %OrderNo)

I was trying something like:

=Count(DISTINCT{$<[CloseReason]<>{CNX}>} %OrderNo) but there is an error.

I could use:

=Count(DISTINCT %OrderNo) - Count(DISTINCT{$<[CloseReason]={CNX}>} %OrderNo)

This works but i'm sure there is a "cleaner" way to do this.

Any advice would be appreciated.

Thanks

Re: Set Analysis - Exclude reason code

tresB — Mon, 05 Dec 2016 10:03:50 GMT

Exclusion symbol here is '-' . Try:

=Count(DISTINCT{$<[CloseReason] -={CNX}>} %OrderNo)

Re: Set Analysis - Exclude reason code

Mon, 05 Dec 2016 13:14:23 GMT

HI Tresesco,

I've just tried that. I know i have a total of 2680 unique order numbers, of which 15 are with CNX. This should leave me with 2665 with the excludion.

The expression you have suggested leaves me with a count of 15 and not 2665 (2680-15).

Thanks

Mitesh

Re: Set Analysis - Exclude reason code

tresB — Tue, 06 Dec 2016 04:45:59 GMT

Have you noticed and placed the minus symbol ('-' in red) properly in the set expression?

Re: Set Analysis - Exclude reason code

jonathandienst — Tue, 06 Dec 2016 04:58:01 GMT

And don't leave a space between the - and = sign. The exclude operator is -=.

Re: Set Analysis - Exclude reason code

Anil_Babu_Samineni — Tue, 06 Dec 2016 05:15:20 GMT

You may try this too

=Count(DISTINCT{$<[CloseReason] = {"*"} - {"CNX"}>} %OrderNo)

=Count({$<[CloseReason] = {"*"}-{CNX}>} DISTINCT %OrderNo)

Set Analysis - Exclude reason code

Re: Set Analysis - Exclude reason code

Tue, 06 Dec 2016 11:33:12 GMT

I've copied and pasted what i have below:

=Count(DISTINCT{$<[CloseReason] -={CNX}>} %OrderNo)

Still only gives me 12 records, not 2665.

Re: Set Analysis - Exclude reason code

jonathandienst — Wed, 07 Dec 2016 08:44:59 GMT

Are you sure that there are 2665 DISTINCT records?