Permanently Filter out Data in Certain Visualizations

[mskusace]

I have a data set that has "STATUS" as a field. There are 4 possibilities for "STATUS". For one of my tables, I want to permanently filter out one particular "STATUS" so it does not even show up. However, I do not want to remove it from the data as I may use it in other visualizations or calculations. 

Below is a small sample of what my data looks like. I would like to filter out the "FINAL" so it does not appear at all in my table, nor is it an option for users to display on that particular table. It this possible and if so, how would I accomplish this?

ID	COLOR	STATUS
WA13	GREEN	FINAL
WZ14	RED	DUE
DB59	ORANGE	DUE
KF25	GREEN	CURRENT

[rwunderlich · Accepted solution]

If STATUS is the only dimension in the chart you can filter out FINAL and keep the nulls by defining the Dimension column as:

=aggr(only({<STATUS-={'FINAL'}>}STATUS), STATUS)

If you have multiple Dimensions, it's probably easier to add the set expression to the Measure.  

Can you  share what your final chart will look like?

-Rob

[mskusace]

There are multiple elements I can filter out. 

For example:

ID	COLOR	STATUS	DEPARTMENT	TYPE
WA13	GREEN	FINAL	IN	SMALL
WZ14	RED	DUE	DE	LARGE
DB59	ORANGE	DUE	OC	LARGE
KF25	GREEN	CURRENT	DE	MEDIUM
DZ42	YELLOW	(NULL)	PN	SMALL

On the dashboard (Table), I would like the user to be able filter down by any of those fields (ID, COLOR, STATUS, DEPARTMENT, or TYPE). However, I just don't want STATUS = FINAL to show at all on the table the user sees, but I do want NULL values to show. 

Your =aggr(only({<STATUS-={'FINAL'}>}STATUS), STATUS) solution appears to work! It has filtered out all the "FINAL" "STATUS" values. 

Could you explain what you mean by "If you have multiple Dimensions, it's probably easier to add the set expression to the Measure."?

Your solution is perfect though.

[rwunderlich]

Let's assume that your example table had an additional Measure column that was a count of Orders. 

=Count(Distinct OrderId)

Rather than putting the set expression on the Dimension, you could add the set in the measure like this:

=Count({<STATUS-={'FINAL'}>}Distinct OrderId)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[mskusace]

Thank you!

What if I wanted to filter out a couple more things or ONLY display certain statuses?

 Let's say I wanted to show "RED" and "ORANGE", but not "GREEN" or "YELLOW". 

Also, what if I only wanted to show (NULL) or blank values?

[rwunderlich]

You can achieve all those with set expressions inserted in the Measure (usually, there are times when you want to put them in the dimension, but the set syntax is the same). 

Some resources for learning set syntax:

Qlik Sense help: http://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAna...

A good doc example doc here: https://community.qlik.com/cyjdu72974/attachments/cyjdu72974/qlikview-documents/8352/1/Les%20set%20a...

The Set Expression option of the Qlik Sense expression editor is pretty helpful, although currently only able to handle simple selections. 

Let us know if you have any specific questions after looking at the doc.  

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com