Use case
Developers often want one input to control many properties in an application: metrics or dimensions in a chart, labels, titles, visibility, etc. The chart in the image above is a simple example, with dynamically populated dimensions and measures.But what if you wanted to be able to select a metric to populate all of the charts on a sheet, each with its own variations (CYTD, PYTD, Delta, Delta %, etc.)? How would you implement that?
Common solution for the input: Always One Selected Value
A common approach for setting the user input is loading the desired values into a field, then enabling the field setting "Always One Selected Value". You then interact with the field in a list box or filter pane, as you would any other field. (This was a more common solution in QlikView, while this field setting was only recently added to Qlik Sense.) The selected field is usually prefixed with a HidePrefix to prevent it from always appearing in the current selections.
This is done with island tables, or tables without any keys or associations. Island tables are not always bad things, but they can be suboptimal when you make selections in them for reasons explained below
Always One Selected Value Performance: 👎
Making a selection in an island table changes the selection state of the application, causing everything to recalculate -- even things that were not affected by the selection. This causes pointless CPU cycles and re-caching the same results in RAM, multiple times. Rob Wunderlich did a good job outlining the performance downside here.Always One Selected Value Maintenance: 👎
Changing the values behind the Always One Selected Value field in an island table is generally not a problem. However, any time the data are removed from the application, the field setting is disabled and must be manually re-enabled. This can be a problem when you are migrating an application from one environment to another and remove the data during that process or you open a large application without data in the Hub.An aside: I don't particularly like how filter panes look in Qlik Sense when this setting is enabled. The feedback to the user about how to interact with it is not as intuitive as seeing a button, radio button, or dropdown.
Common solution for the business logic: embedded in Pick/Match expressions
NB: Everything in this section regarding performance and maintenance is equally true of nested IF statements.
For some Qlik developers, the Pick and Match functions are like peas and carrots. Used together, the expression contains two lists: the possible input values
and the corresponding dimension or measure values to use. A dynamic
chart expression behind the first image in this post might look something like this:
Pick(
Match('$(v_Expression)', '$ Variance', '$ Sales', 'Qty'),
Sum(Variance), Sum(Sales), Sum(Qty)
)
Note that the orders of values in the two lists must match, as corresponding pairs. Match returns a number, 1-n, then Pick uses that number to choose a corresponding value.
Pick/Match Performance: 👎
The way the Pick function (and other conditional functions, like IF) work in Qlik is that every argument is evaluated, whether it is returned or not. The expressions written in both the true and false arguments are evaluated, even though only one ends up displayed, adding calculation overhead. In the example above, all three measures are computed, even though just one is displayed, so 3x the necessary work is done by Qlik.Pick/Match Maintenance: 👎
Starting down the Pick/Match path creates a tangled web of decentralized code. There is an input that affects some number of other expressions, with no obvious relationship. If you need to add, remove, or edit the options, you have to edit every expression that refers to the input, which is time-consuming, if you can find all of them.To see examples in the wild, look up "pick match" on Qlik Community. Or, here is the beginning of a 79-line, 7087-character expression I came across at a customer. I recommended that they re-design the entire implementation of the interface because it would be nearly impossible to detect a bug, troubleshoot a bug, or for anybody other than the original developer to change it. (That's to say nothing of the performance.)
if(C_KPI_Order=1,
NUM(
pick(vToggle,Sum({<[KPI] = {'ORDER'},WeekNum={'$(vCurrentWeek)'}>}[Volume])-Sum({<[KPI] = {'ORDER'},WeekNum={'$(=vCurrentWeek-1)'},[Data As Of]=>}[Volume]),
Sum({<[KPI] = {'ORDER'},WeekNum={'$(vCurrentWeek)'}>}[REV])-Sum({<[KPI] = {'ORDER'},WeekNum={'$(=vCurrentWeek-1)'},[Data As Of]=>}[REV])),'##0.0'),
if(C_KPI_Order=4,
NUM(pick(vToggle,Sum({<[KPI] = {'SHIP'},WeekNum={'$(vCurrentWeek)'}>}[Volume])-Sum({<[KPI] = {'SHIP'},WeekNum={'$(=vCurrentWeek-1)'},[Data As Of]=>}[Volume]),
Sum({<[KPI] = {'SHIP'},WeekNum={'$(vCurrentWeek)'}>}[REV])-Sum({<[KPI] = {'SHIP'},WeekNum={'$(=vCurrentWeek-1)'},[Data As Of]=>}[REV])),'##0.0'),
if(C_KPI_Order=7,
NUM(
pick(vToggle,Sum({<[KPI] = {'ORDER'},WeekNum={'$(vCurrentWeek)'}>}[Volume]),Sum({<[KPI] = {'ORDER'},WeekNum={'$(vCurrentWeek)'}>}[REV]))/
pick(vUsedTarget,
...
My preferred solution: variable lookup in an island table
The approach I frequently use mitigates the downsides of the above and supports generally good coding practices and maintainability. There are two components:
All business logic is stored in an island table. This contains a row for each option in a dropdown menu or scenario you wish you capture. Whenever you want to add more metadata, like variations of the same metric for different time periods, metric formatting, or business definitions to display in chart subtitles, you can just add more columns to the table.
However, selections are never made in this island table. A variable extension or input box is used to change a variable value, which drives simple lookups of values in the island table. You can set the variable equal to an index value or the label itself. To use the example above:
Only({<_MeasureName1 = {'$(v_ValueInDropdown)'}>} _MeasureFormula1)
Once written, that expression would never need to be revisited.
Variable/Island Table Performance: 👍
Because selections are never made in the island table, the selection state never changes. This is better than the extraneous CPU and cache associated with Always One Selected Value.Because there are no conditional expressions -- each row represents a condition -- only the expressions that need to be displayed are evaluated. All the waste has been trimmed.
Variable/Island Table Maintenance: 👍
Centralizing the business logic in the island table rather than distributed over a series of formulas enables you to make changes in that centralized location rather than throughout the UI and reduces the overall volume of code. It also enables you to see the underlying mechanics at a
glance, without looking at scattered
UI objects.
If at some point the data are reduced from the application, like during a migration or in a backup of a file, there is no need to re-set the the Always One Selected Value field settings. And each reload can reinitialize the default variable values in the script.
The one downside is that variable values aren't captured as part of a bookmark, whereas this does work for Always One Selected Value. This means that restoring a bookmark will not restore whatever variable selections you had made at the time the bookmark was created. Barring one-off circumstances, I feel that the upsides outweighs that downside of the approach.
I did a followup post here with more implementation details.