As we all know, businesses love ad hoc reporting. Ad hoc reports empower the business users to create reports which caters to their needs. In this post I am not going to explain how this can be done in Qlik (as this has been explained in Customizable Straight Table by Jennell McIntire), but talk about one of the important settings where developers run into issues.
Sometimes we have dimension/measure names which are subsets of other dimension/measure names. For example, let’s say in our ad hoc report we have these dimensions, in addition to others, available for users to select
- Name
- First Name
- Last Name
For making these dimensions conditional, we have used expressions like the following
- =SubStringCount(Concat(_dimension, ‘|’), ‘Name’)
- =SubStringCount(Concat(_dimension, ‘|’), ‘First Name’)
- =SubStringCount(Concat(_dimension, ‘|’), ‘Last Name’)
The problem that we see with the above expression is that Name dimension will show up when either First Name or Last Name dimension is selected. This happens because the word ‘Name’ appears in both First Name and Last Name and SubStringCount will return 1 for anytime it sees the word ‘Name’ and will display the Name dimension. In general, anytime we see a subset of the word used as another dimension/measure name, we will run into this issue.
So, what can we do to make our expression smart enough to differentiate between a selection in Name vs First Name vs Last Name? We can change our conditional expressions to look something like this
- =SubStringCount(Concat(‘|’ & _dimension & ‘|’), ‘|Name|’)
- =SubStringCount(Concat(‘|’ & _dimension & ‘|’), ‘|First Name|’)
- =SubStringCount(Concat(‘|’ & _dimension & ‘|’), ‘|Last Name|’)
In order to differentiate between selection in Name, we are now adding a beginning pipe symbol and an ending pipe symbol by changing the Concat() function from Concat(_dimension, ‘|’) to Concat(‘|’ & _dimension & ‘|’). Now when we check for the dimension name, we don’t just check for Name, but we check for |Name|. By adding pipe symbols on both sides of the dimension name, we have made sure that dimension name is not a subset of another dimension.
Note 1: We can also use GetFieldSelections() function instead of Concat to pick selected values within _dimension or _measure field.
- =SubStringCount(‘|‘ & GetFieldSelections(_dimension, ‘|‘, 100) & ‘|‘, ‘|Name|‘)
- =SubStringCount(‘|‘ & GetFieldSelections(_dimension, ‘|‘, 100) & ‘|‘, ‘|First Name|‘)
- =SubStringCount(‘|‘ & GetFieldSelections(_dimension, ‘|‘, 100) & ‘|‘, ‘|Last Name|‘)
Expression might look a little more complex, but the underlying idea stays the same where we are adding pipes around the selection to differentiate it from other dimensions.
Note 2: To avoid using this same expression multiple times, we can use variable with parameter. The variable can be defined as
SubStringCount(Concat(‘|’ & [$1] & ‘|’), ‘|$2|’)
or
SubStringCount(‘|‘ & GetFieldSelections([$1], ‘|‘, 100) & ‘|‘, ‘|$2|‘)
and can be used as (assuming the variable name is vSelectionCheck)
- =$(vSelectionCheck(_dimension, Name))
- =$(vSelectionCheck(_dimension, First Name))
- =$(vSelectionCheck(_dimension, Last Name))
Let us help you take your Qlik skills to the next level!
About the Author:
