Solved: Permanently Filter out Data in Certain Visualizati... - Qlik Community

mskusace · ‎2019-02-15

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 · ‎2019-02-15

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

View solution in original post

pradosh_thakur · ‎2019-02-15

Sum ({<STATUS-={'FINAL'}>}Sales)

Use the set analysis {<STATUS-={'FINAL'}>} in the expression of yours

Learning never stops.

mskusace · ‎2019-02-15

Thank you for the reply. Where would I put that? I am not familiar with set analysis. I recently received another answer that used set analysis as well.

pradosh_thakur · ‎2019-02-15

Can you tell me your dimension and expression . It will be easier o help as you are new to set analysis.

Learning never stops.

mskusace · ‎2019-02-15

My dimension is [STATUS] and it can be "FINAL", "DUE", or "CURRENT". I do not want to "FINAL" to show up on this particular table at all.

The field is "=[STATUS]".

I hope that is what you were asking. Please let me know if you need more information!

emrekaya · ‎2019-02-15

You can use if condition in the table dimensions.

if(status=final,null(),status) and then you can uncheck the " show null values"

mskusace · ‎2019-02-15

Excellent, that did it!

What if there is a case where I would like it to show NULL values in case there is a pre-existing NULL value that I would like to see?

pradosh_thakur · ‎2019-02-15

 if([STATUS] ='FINAL',Null(),[STATUS])

Try the above.

Learning never stops.

emrekaya · ‎2019-02-15

You can leave the null values on the table but then it may be confusing. I have no solution for the second part of the question.

If you accept my solution as a solution, I would be glad. Thanks 🙂

mskusace · ‎2019-02-15

If my table was like the table below:

ID	COLOR	STATUS
WA13	GREEN	FINAL
WZ14	RED	DUE
DB59	ORANGE	DUE
KF25	GREEN	CURRENT
DZ42	YELLOW	(NULL)

I would want to display DZ42, but NOT WA13 because the STATUS = FINAL. Would that be possible through another method?

Permanently Filter out Data in Certain Visualizations