Set Analysis (Excluding Values)

[#1]

=

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

[#2]

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

Should do it...

[Miguel_Angel_Baeyens]

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])

[#4]

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

[Miguel_Angel_Baeyens]

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'.

[#6]

Finally solved the problem

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

[Miguel_Angel_Baeyens]

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])

[#8]

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