Set analysis to exclude two dimensions

[alisonpwallis]

Dear All,

I am trying to create a bar in my chart that shows the sum of a dimension FTE where the records do not have values for two different categories. I have tried using set analysis with (-=) but this doesn't work. I want to do this:

=sum({$ <[Application Status]-={"U/","U/F","U/D","U/I","C/","C/F","C/D","C/I","W/","R/"},[Description]-={"Action on ARC Pending", "Borderline", "Invited to interview", "On Central Hold", "Referred to department", "Withdrawn"} >}  [FTE])

So I only want the sum of FTE where the application status is not in that list (that is the complete list so anyone else has no status) and the Description is not in the list too (again, this is the complete list and anyone else has no description).

The above passes the syntax check but with the red line underneath but it doesn't show anything in the chart and there are quite a few who meet the two conditions.

Thanks for your help

Alison

[tresesco]

Try like:

=sum({$ <[Application Status]-={"*"} , Description]-={"*"}>}  [FTE])

[MK_QSL]

=sum({$ <[Application Status]-={'U/','U/F','U/D','U/I','C/','C/F','C/D','C/I','W/','R/'},[Description]-={'Action on ARC Pending', 'Borderline', 'Invited to interview', 'On Central Hold', 'Referred to department', 'Withdrawn'} >}  [FTE])

[Gysbert_Wassenaar · Accepted solution]

Are you trying to get the sum of FTE values that have no application status and no description, i.e. those with 'null values'? Nulls are quite tricky since they can't be selected. The best way to deal with them is to replace them with real values in the script. See this document: NULL handling in QlikView.

If you want to do this with set analysis you can try something like sum({1-$<[Application Status]={'*'},Description={'*'} >} FTE).

[arjunkrishnan]

Hai Frds,

I think Your will Expression Fine..

=sum({$ <[Application Status]-={"U/","U/F","U/D","U/I","C/","C/F","C/D","C/I","W/","R/"},[Description]-={"Action on ARC Pending", "Borderline", "Invited to interview", "On Central Hold", "Referred to department", "Withdrawn"} >}  [FTE])

Pls Provide Some Sample Data ...That Very Help to as  

[#6]

If you do not want the application status and description selection in your report then try:

=sum({< [Application Status]=,[Description]=} >}  [FTE])

You will get the sum(FTE) which will not change on the selection on above fields.

[alisonpwallis]

This is a great idea and I have done it this way as I couldn't get the other ways to work. Thank you very much for the link and the info.

Alison