Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. 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