Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set Analysis (Excluding Values)

=

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



Tags (2)
7 Replies
Highlighted
Not applicable

Set Analysis (Excluding Values)

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

Should do it...

Highlighted

Set Analysis (Excluding Values)

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


Highlighted
Not applicable

Set Analysis (Excluding Values)

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

Highlighted

Set Analysis (Excluding Values)

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

Highlighted
Not applicable

Set Analysis (Excluding Values)

Finally solved the problem

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



Highlighted

Set Analysis (Excluding Values)

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


Highlighted
Not applicable

Set Analysis (Excluding Values)

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