topic Re: Filtering on a dimension from a PICK list, filters on all, instead of just the returned dimensio in App Development

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

Stefan_dT — Sat, 16 Nov 2024 05:23:50 GMT

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?

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

Simon_Astakhov — Wed, 03 Jul 2019 14:39:10 GMT

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.

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

Stefan_dT — Thu, 04 Jul 2019 03:59:16 GMT

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.

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

Stefan_dT — Mon, 08 Jul 2019 10:58:15 GMT

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.

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

georgio_hb — Wed, 22 Apr 2020 06:04:48 GMT

Hello, Did you find any solution for that ?

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

Stefan_dT — Wed, 22 Apr 2020 06:30:13 GMT

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

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

lorenzoconforti — Wed, 22 Apr 2020 10:06:07 GMT

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

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

Stefan_dT — Fri, 15 May 2020 13:41:52 GMT

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

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

lorenzoconforti — Fri, 15 May 2020 15:03:56 GMT

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