topic Set Analysis (Excluding Values) in QlikView

Set Analysis (Excluding Values)

Tue, 24 Nov 2009 17:16:30 GMT

Sum({$<[Contract_Type]={"*"}-{"*IAV*"}>}[Invoice_Net])

I am trying to ammend the formula above to exclude [Call_Type] = {"PM"} as well as contract types of IAV but i do not seem to be able to get the syntax correct.

To recap I need all records for a selection excluding any contracts with iav in the name and excluding call types PM.

Can anyone give me the correct syntax

Thank you

Set Analysis (Excluding Values)

Tue, 24 Nov 2009 17:24:05 GMT

Sum({$<[Call_Type]-={'PM','IAV'}>}[Invoice_Net])

Should do it...

Set Analysis (Excluding Values)

Miguel_Angel_Baeyens — Tue, 24 Nov 2009 18:38:16 GMT

Checking the syntax:

Sum({$< [Contract Type] -= {"*IAV*"}, [Call_Type] -= {'PM'} >} [Invoice_Net])

should work. Note that "*IAV*" follows your "any contracts with iav in the name". If there is a IAV Contract_Type, change the above expression to

Sum({$< [Contract Type] -= {'IAV'}, [Call_Type] -= {'PM'} >} [Invoice_Net])

Set Analysis (Excluding Values)

Tue, 24 Nov 2009 19:15:54 GMT

I think we are close but my original question was not clear enough.

I need to see everything except (contracts containing IAV that AND have a call type of PM)

Regards

Set Analysis (Excluding Values)

Miguel_Angel_Baeyens — Tue, 24 Nov 2009 19:45:58 GMT

We just need to know the name of field that contains Names of Contracts (which in turn may contain "*IAV*"), but the syntax would be

Sum({$< [CONTRACT_NAME_FIELDNAME] -= {"*IAV*"}, [Call_Type] -= {'PM'} >} [Invoice_Net])

that means "total sum of current selection BUT contracts with "*iav*" in its name and [Call_Type] = 'PM'.

Set Analysis (Excluding Values)

Tue, 24 Nov 2009 22:59:07 GMT

Finally solved the problem

=sum([Invoice_Net])-Sum({$<[Contract_Type]={"*IAV*"},({$<[Call_Type]={"PM"}>}[Invoice_Net])

Set Analysis (Excluding Values)

Miguel_Angel_Baeyens — Tue, 24 Nov 2009 23:34:45 GMT

Besides some syntax errors in your expression, I'm sure you can get what you want with

Sum({$< [Contract_Type] -= {"*IAV*"}, [Call_Type] -= {"PM"} >} [Invoice_Net])

Set Analysis (Excluding Values)

Tue, 24 Nov 2009 23:49:25 GMT

I did try and I do not now why but for some reason any variation I tried on the formula would not exclude contracts that where IAV and had a call type of PM.

Thanks for your help