Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.
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
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?
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