You obviously know the Pareto principle – 80% of outcomes are driven by 20% of efforts. QlikView offers numerous tools that allow visualizing the Pareto principle. Here are just a few that come to mind:

• It’s quite easy to create a Pareto report and show cumulative contribution of Customers (Suppliers, Products..) to the overall % of Sales (Purchases, Profits…)
• Using Dimension Limits, we can limit a chart to only show those values that accumulate to a certain % of the total.
• Using Actions, we can apply a Pareto selection on any field based on any expression.

And yet, one commonly asked question used to stump me until recently. How can I use a single calculation to show what number of Customers (Suppliers, Products, …) contribute to 80% of my Sales (Purchases, Profits…)? All I want is a simple text object that states:

NNN Customers Contribute to 80% of Sales

At first, I was thinking of an elaborate AGGR() function, but then I’d have to find a way to accumulate values within the AGGR(), and to sort the pre-aggregated values in a specific order. If such a solution exists, I couldn’t come up with it.

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.

Recently, I was asked the same question again, this time by my daughter. This time, the idea surfaced almost instantly (probably because I really didn’t want to lose face in front of my daughter) – Alternate States!

If we create an Alternate State and only use it for the Pareto calculation, then we can easily use Actions to make a Pareto selection there, and then count the number of available Customers in the Alternate State – as many times as we need to!

If we want this calculation to be sensitive to all other selections that may exist in the default state, we can use another Action to copy selections from the default state to the Pareto state. We also need to remember to clear existing selections in the Customer field prior to applying the Pareto selection, otherwise every new selection (i.e. every click on a button) will get applied on top of the previous selection.

The only caveat is that this calculation involves using Actions, and therefore it needs to be triggered somehow. Many of the “automated” triggers, such as “On Open” or “On Activate Sheet”, may not work in the AJAX environment. The most reliable, even if slightly annoying, solution is to add a “Recalculate” button that can be pressed every time the calculation needs to be refreshed.

It might be tempting to trigger the Actions on the event “On Any Select”, however I would not recommend using this event too much. Keep in mind that Actions that are triggered on any selection, will get performed on every single click of the mouse, eventually causing a substantial performance overhead.

So, here is the step-by-step recipe for calculating the number of Customers that make up 80% of Sales:

First, create an Alternate State and call it Pareto.

Then, create a button and name it Recalculate. Add the following Actions that should be performed when the button is clicked:

• Copy State Contents from the default state $ to the alternate state Pareto.
• Clear Field. Specify Customer as the Field and Pareto as the Alternate State.
• Pareto Select. Specify Customer as the field and the expression of Sales as the Expression. Specify 80 as Percentage (surprisingly, percentages are formatted here as whole numbers of percents and not as decimal numbers).

Finally, use something like the following calculation in your Text Object that states the number of customers that make up 80% of your Sales:

= IF( count({Pareto} distinct Customer) = count (ALL Distinct Customer), 
      'Press the Recalculate button to perform the Pareto calculation',
      num(count({Pareto} distinct Customer)) & ' Customers make up 80% of Sales'
 )

Notice the condition that I used to force the user to press the button at least once. If the number of Pareto customers is the same as the total number of customers in the whole document, it’s safe to conclude that the Pareto selection was not made in the alternate state.

This trick may sound obvious to you. To me, it appears to be yet another example of how Alternate States can be used creatively for a variety of unusual analytical solution.

If you enjoy the content of my blog, please check out my new book QlikView Your Business.   Also, check out the agenda of the upcoming sessions of Masters Summit for QlikView in New York and in Copenhagen and find out if you are ready to take your QlikView skills to the next level.