It’s fascinating how obvious things often turn out to be “not-too-obvoius”…
We know that the “Back” and “Forward” buttons in QlikView can take you back to your previous selections and forward to more recent ones. So, if you made 2 consecutive selections, pressing “Back” twice should return you to the starting point? Well, most of the time, but not always…
We learned it the hard way, when we tried to make use of the Pareto selection, that’s only available through Actions (look it up, it’s pretty cool).
So, the initial question was: “How many customers make up 80% of my sales?”. This pretty trivial question is not so trivial to answer, at least not programmatically.
Most sales analysis applications include some form of “Pareto” view. We usually show a list of Customers (Products, Salespersons, …) with their % of total, and a Cumulative % of total, sorted by the same %. Time after time, the Pareto rule works like a charm, showing that relatively few Customers make up the top 80% of sales, and a huge number of other customers make up the remaining 20%. So, it’s very easy to count the lines visually and to see for yourself how many customers make up the 80%. However, if we wanted to calculate it in a free-standing expression and show it as a KPI metric, the job is not so simple…
So, we decided to resort to a tricky work around. What if we triggered a set of Actions. First Action would facilitate the Pareto Selection, then the second action would calculate the number of selected Customers and store the result in a variable, and then the third Action would reverse the previous selection using the Action “Back”. Sounds pretty logical, right?
Well, once we coded the actions and we fired the trigger, we could see that the Pareto Select was, in fact, performed, but the variable didn’t get populated and the Back action didn’t reverse the selection.
We tried all sorts of tricks. We blamed timing and multi-threading, and we tried to perform the actions one by one. Only then, watching the actions one by one, could we see the problem. Here is what happened:
Next, we tried it manually… Created an Input Box with a variable, entered a new value to the variable, then pressed Back, and voila – the value got reversed… But why?
Only then (of course) we decided to open the Manual and we read about the action Back. Here is the definition (quoting QlikView Help)
Back Reverts to the preceding logical state. Applies to value selections and all the commands in the Selections menu…
We’d say this is a little bit ambiguous and perhaps a little bit under-documented. While it’s possible to perceive that changing a value of a variable represents a different “logical state”, it wouldn’t necessarily be our initial assumption. Besides, it has nothing to do with the “value selections and all the commands in the Selections menu”.
Lesson learned: when using “Back” and “Forward” actions, be aware of variables and possible changes in their values. Those will also get counted for, as “Logical states”…
Would this work?
Step one: save selections in a bookmark
Step two: pareto select
Step three: calculate no. of customers and store in variable
Step four: activate the bookmark
Step four wouldn’t be a Back action, so if the variable isn’t stored in the bookmark it ought to keep its value. Or am I missing something?
[…] Then, I was considering a sequence of Actions that involved applying a Pareto selection, counting the available Customers and storing the count in a variable, and then restoring the previous selections by using the Back action. At that time, I discovered (at least to myself) that the Back action is not only reversing selections, but also cancels any changes in variable values. That issue had triggered my earlier article Q-Tip # 3. […]