Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alisonpwallis
Creator
Creator

Set analysis to exclude two dimensions

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
tresesco
MVP
MVP

Try like:

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

MK_QSL
MVP
MVP

=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

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


talk is cheap, supply exceeds demand

View solution in original post

arjunkrishnan
Partner
Partner

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  

Not applicable

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
Creator
Creator
Author

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