A reader reached out using the contact page to ask how to keep the variable dropdown values from this post (Please use this instead of Always One Selected or Pick/Match) in sync with the metrics in the island table, which made me realize I omitted instructions to complete this high-performing, low-maintenance solution. If you have not read that post, I recommend reviewing it first to understand the why of this solution.
In this post, I will demonstrate how to automate creating the variables that make the whole thing work.
Load metric island table
Step 1 is to load the island table. In this basic example, I use an inline load, but in practice it is better to load from a file or database because of difficulties using some notation and characters (square brackets, dollar-sign expansion) in the bare script.
Derive variables
Next, we will create two variables, both dynamic based on the contents of our metric island table. Once we write this code, we will not have to revisit it, even if the contents of the metric island table change.
v_MetricName is the value the dropdown selection changes. For this solution, it will always be equal to one of the metric names.
Its value can be determined immediately after the metric table load, and I like to default to whatever metric is listed first in the table.
v_MetricNameList is a string of the possible metric names, concatenated together with a vertical bar delimiter. It is used to populate the dropdown list. This could be calculated in the front end but sticking to our principle of pushing work upstream (especially when we already know what the values will be, at the time of reload), we will create it in the script.
This variable value requires a load to concatenate values together into a single value, and then the resulting value is captured.
Here are the results:
Pulling it together
Now that we have the island table and the two variables, we can combine it in the front end to create our dynamic output.
First, we will set up the variable extension with the variable we are changing (v_MetricName) and the variable with the list of values to populate the dropdown (v_MetricNameList).
Now, we can use the output of the variable extension interactivity to populate metrics in charts dynamically. Here is an example of dynamically returning the formula from this island table:
Detail of the Expression:
I have also attached the sample QVF I used for the screenshots. In practice, you could create additional columns with variations of the metric, like the metric value CYTD and PYTD, or add metadata to dynamically display, like a business definition. But those changes only require creating some more columns and formulas without fundamentally revisiting the architecture of the solution.
Metric Island Table Example.qvf
Thank you to that reader for reaching out and making me aware so I could provide a better, more complete solution to follow.