Filtering on a dimension from a PICK list, filters on all, instead of just the returned dimension

[Stefan_dT]

Good day

I have a dynamic dimension I use in a bar graph, based on the value of a variable.  This variable gets set by the end-user's choice of KPI to focus on.  It is used to change colour schemes, show/hide measures in tables etc...  All works fine.

However when I click on a bar in the bar graph using this dynamic dimension (see code below), it doesn't just filter on the "active" dimension, but on all.  

=PICK(MATCH('$(vKPI)'  ,'Sites','Revenue','ARPS','Data','Voice','MSI','nNPS','Quality'), 
                        [Max Tech Data Utilisation % Bracket]
                       ,[Revenue Growth % Bracket]
                       ,[Revenue Growth % Bracket]
                       ,[Data Traffic Growth % Bracket]
                       ,[Voice Traffic Growth % Bracket]     
                       ,[MSI % Bracket]           
                       ,[nNPS Bracket]            
                       ,[Quality % Bracket])
                       

So for example, if the vKPI variable = 'Revenue', the dimension returned would be [Revenue Growth % Bracket].  If I click on a bar, eg the "1 - 10%" bracket, my dataset gets filtered on all 8 bracket dimensions:

FilteringThe result in the selections bar

How can I limit the filter action to only filter on the selected dimension from the pick list?

[Simon_Astakhov]

Hi!

Just put it inside another dollar sign like:

=$(=PICK(MATCH('$(vKPI)'  ,'Sites','Revenue','ARPS','Data','Voice','MSI','nNPS','Quality'), 
                        [Max Tech Data Utilisation % Bracket]
                       ,[Revenue Growth % Bracket]
                       ,[Revenue Growth % Bracket]
                       ,[Data Traffic Growth % Bracket]
                       ,[Voice Traffic Growth % Bracket]     
                       ,[MSI % Bracket]           
                       ,[nNPS Bracket]            
                       ,[Quality % Bracket]))

 Not shure you need second =, but try. It will calculate required fiel name inside $() dollar and inser single name inside calculated dimension.

[Stefan_dT]

Thanks Simon, but Qlik don't seem to like this syntax.  Without the 2nd "=" it gives me an "Invalid Dimension" and with it, it only returns nulls.

[Stefan_dT]

Managed to get the format sorted of putting it inside a $:

={'$(=PICK(MATCH(''$(vKPI)''   ,''Sites'',''Revenue'',''ARPS'',''Data'',''Voice'',''MSI'',''nNPS'',''Quality''), 
                         [Max Tech Data Utilisation % Bracket]
                        ,[Revenue Growth % Bracket]
                        ,[Revenue Growth % Bracket]
                        ,[Data Traffic Growth % Bracket]
                        ,[Voice Traffic Growth % Bracket]     
                        ,[MSI % Bracket]           
                        ,[nNPS Bracket]            
                        ,[Quality % Bracket]))'}

 This still gives an error msg in the expression editor, but actually worked the same as before.  Unfortunately the erroneous behavior of applying a filter on all fields remains.  

[georgio_hb]

Hello, Did you find any solution for that ?

[Stefan_dT]

Hi Georgio_hb - no sorry, I haven't.  This really is a pain.

[lorenzoconforti · Accepted solution]

You just need to use a couple of variables

As a dimension in your bar chart, use the following:

='$(dimensionUse)' [note that this is exactly how it needs to show in your field box; if you go into the expression editor it will add an extra square brackets which will make it stop working; depending on how your dimensions are set up you might need to remove the equal sign; see picture below]

The dimensionUse use variable will be defined as:

$(dimensionDyn)

The dimensionDyn variable will be:

=PICK(MATCH('$(vKPI)' ,'Sites','Revenue','ARPS','Data','Voice','MSI','nNPS','Quality'), '[Max Tech Data Utilisation % Bracket] ','[Revenue Growth % Bracket]' ,'[Revenue Growth % Bracket] ','[Data Traffic Growth % Bracket]' ,'[Voice Traffic Growth % Bracket]' ,'[MSI % Bracket] ','[nNPS Bracket]' ,'[Quality % Bracket]')

You need these nested variables to get around the need of single quotes. Once done, when selecting in your chart, it should just limit the selection to that specific dimension

You can also see it implemented here:

https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Qlik-Sense-UK-Maps/ta-p/1634345

[Stefan_dT]

Thanks Lorenzoconforti - this is working perfectly!!!!  Would never-ever have thought of this on my own.

[lorenzoconforti]

Not a problem; it was causing me some headaches until I got it working this way