topic Ignore set expression dimension filter selection in other measure in QlikView

Ignore set expression dimension filter selection in other measure

Young_Buck — Wed, 11 Sep 2024 21:39:26 GMT

I have a dimension which has many values and so have written a set expression to categorize and roll up these dimensions into easier to read categories, for example:

=If([Dimension 1]='A000' ,'A',

If([Dimension 1]='B006' Or [Dimension 1]='H001' Or [Dimension 1]='B009','B',

If([Dimension 1]='C003' Or [Dimension 1]='D005','C',

etc.

))))))

So now I have created a new rolled up category for Dimension 1 which I then name Category 1. If I were to select one of the categories, like B, it would filter my sheet and appear in my selections (although the selection would show the entire formula and not what I had named it, Category 1).

Is there a way to add a Measure to my sheet that would ignore my set expression selection from above?

I know that if I were to add into a set expression "[Dimension 1]=" that would ignore any Dimension 1 selections for the Measure. However, it does not seem to work inside of Category 1. For example: selection of "Category 1 = A" will not be ignored even though it is only selecting for A000 within that category. If I selected "Dimension 1 = A000", the measure will ignore that selection because of my "[Dimension 1]=" set expression.

Re: Ignore set expression dimension filter selection in other measure

rwunderlich — Thu, 12 Sep 2024 00:06:14 GMT

Your filter is exhibiting the "Fields on the Fly" behavior.
https://community.qlik.com/t5/Design/Calculated-Fields/ba-p/1694383

You need to force the selection to be in the [Dimension 1] field. You can do this on one of two ways:

Wrap the dimension expression in a CalcDim() function:

=CalcDim(
If([Dimension 1]='A000' ,'A', 
If([Dimension 1]='B006' Or [Dimension 1]='H001' Or [Dimension 1]='B009','B',
If([Dimension 1]='C003' Or [Dimension 1]='D005','C'
' '
))))

or wrap in an Aggr() function that specifies what field(s) you want to make the selection in:

=Aggr(
If([Dimension 1]='A000' ,'A', 
If([Dimension 1]='B006' Or [Dimension 1]='H001' Or [Dimension 1]='B009','B',
If([Dimension 1]='C003' Or [Dimension 1]='D005','C'
' '
))),[Dimension 1])

See https://qlikviewcookbook.com/2016/01/scoping-selections-with-aggr/

Note to be too pedantic, but your dimension expression is not a "set expression" -- that's something else.

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

Re: Ignore set expression dimension filter selection in other measure

Young_Buck — Thu, 12 Sep 2024 01:45:29 GMT

This worked!! Thank you so much for the quick reply!