- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Filtering on a dimension from a PICK list, filters on all, instead of just the returned dimension
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:
How can I limit the filter action to only filter on the selected dimension from the pick list?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello, Did you find any solution for that ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Georgio_hb - no sorry, I haven't. This really is a pain.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Lorenzoconforti - this is working perfectly!!!! Would never-ever have thought of this on my own.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not a problem; it was causing me some headaches until I got it working this way