I hate redundancy. Ever since I was introduced to the world of computer science, I have known that if I ever had to perform the same action multiple times in a row, there was a better way to do it. So, when I realized that I needed to make a series of QlikView charts with the same Y axis but differing X axes, I decided there had to be a better way than doing it by hand. By now I have put in far more time than it would have taken to just do it by hand, but that is irrelevant…
The Problem: Create a dynamic QlikView chart that allows the user to specify the X-axis component.
The Approach: Variables to the rescue!
Pressing Ctrl-Alt-v brings up the Variable Overview window, where I make a few variables to loop through my X-axis choices. The important ones here start with “v_”:
v_sortValues: The list of values to use as Dimensions (X-axis components in QlikView). Note that group names are surrounded by double quotes, while field names are surrounded by single quotes. (This took me a long time to figure out…). [For more on grouped dimensions, go to Chart Properties -> Dimensions -> Edit Groups, and play around with it a bit]
v_sortNames: The human-readable names to associate with the sortValues
v_sortIndex: The currently selected sorting option. Note that QlikView starts indexing at 1 (grrr…)
v_sortMaxIndex: How long our sort arrays are
v_sortSelected: The currently selected option. The ‘Pick’ expression is using v_sortIndex to pick the currently selected v_sortValues. We evaluate this here instead of in the chart because… well, because QlikView likes me better when I do it here. Just trust me. Or don’t, and run your own experiments to find out why!
Now, lets use these variables in a chart.
Create a new chart, and give it a dynamic title. We can use our v_sortNames to synchronize the title with the X-Axis, as long as we start the expression with an “=” and concatenate the string with the expression using “&”.
The dynamic X-Axis is even easier… just use our v_sortSelected variable! (in the ‘Dimensions’ tab of the properties table)
Set the Expression to whatever you are trying to calculate.
Now, if we set v_sortIndex to 1, we get a beautiful chart like so:
Almost there! Now we just need a way to switch back and forth between our Dimensions. Lets make a button, with some text saying “Next View”. Next, go to the Actions tab and click “Add”. On the left, choose “External”. On the right, choose “Set Variable”. Click “okay”, then select v_sortIndex as the variable to change. Give it a value of
=If($(v_sortIndex) < $(v_sortMaxIndex), $(v_sortIndex) + 1, 1), so that it will loop 1 through v_sortMaxIndex. Give your button a click, and watch what happens!
Yay!! Multiple views in one graph!! Keep clicking in delight, and watch the graphs load over and over. The most fun you’ve had all night, right? 😉