topic Re: Set Analysis in QlikView

Set Analysis

Fri, 20 Dec 2013 08:22:04 GMT

Hey,

I have a table in which I want to ignore the values of Deleted Items. For deleted items i have a deletion flag. I want to know that how can I apply set analysis (or anyother way if possible) to show all the values except the deleted values. I want deleted values to be ignored.

Regards,

Hannan Tariq

Re: Set Analysis

tresB — Fri, 20 Dec 2013 08:32:20 GMT

Try like:

Sum({<Flag -={'Deleted'}>}Amount) // '-' is used for exclusion.

Re: Set Analysis

sujeetsingh — Fri, 20 Dec 2013 08:39:43 GMT

yup tresco is right by placing '-' after = sign makes it independent of the Flag

Re: Set Analysis

mjm — Fri, 20 Dec 2013 09:24:59 GMT

Hello Hanan,

I believe a better way of doing it to represent 'not equals' (i.e not Deleted) is to use <>

For example

SUM({$<Deleted<>{'DeletedValue'}>}Value)

For this example I have assume Deleted is a field in the dataset you have, and for deletedValue you would define which field values you want to exclude, for example you can use 1 to identify a row to be deleted.

If this example is not what you need, then <> should be used instead of -=, I have personally seen problems with this coding.

Please mark this as answered if my solution has helped.

Thanks,

Mark

Re: Set Analysis

Fri, 20 Dec 2013 11:23:48 GMT

tresesco

I have tried using your method but it gives error.

Sum ({<[Purchasing Deletion Ind._LOEKZ]-={'L'}>}Amount_WRBTR)

and also tried

Sum ({<[Purchasing Deletion Ind._LOEKZ]<>{'L'}>}Amount_WRBTR)

But still not getting what I want.

I need to exclude "Purchasing Deletion Ind._LOEKZ" from my calculation.

Re: Set Analysis

swuehl — Fri, 20 Dec 2013 11:25:56 GMT

Don't get confused with the red underlining in the expression dialog, it's a bug in the syntax checker. The status should say 'expression ok'. Just give it a try.

edit:

I am referring to

Sum ({<[Purchasing Deletion Ind._LOEKZ] -= {'L'}>} Amount_WRBTR)

Re: Set Analysis

Fri, 20 Dec 2013 11:26:18 GMT

Try this,

Sum({<Flag ={*}-{'Deleted'}>}Amount)

Re: Set Analysis

israrkhan — Fri, 20 Dec 2013 11:29:20 GMT

Hi Hannan,

what is "Purchasing Deletion Ind._LOEKZ", is it field name or Flag Value?

if it is your field name and flag value is L for Deleted items then it must work.

Sum ({<[Purchasing Deletion Ind._LOEKZ]= {'*'} - {'L'}> }Amount_WRBTR)

Re: Set Analysis

Fri, 20 Dec 2013 11:46:37 GMT

Thankyou ! this worked perfectly fine for me.