The easy way to automatically scale your charts and tables.
The need to scale charts properly is ubiquitous in the world of BI; if you are dealing with the finances of a large international company, for example, it's both inelegant and wasteful of real estate to show a scale that looks as follows:
Instead, what I almost always do is find the smallest dimension value for a particular chart, and scale the entire chart to that value. Here's what the above chart looks like if we apply that technique, which results in a scale of millions:
Technically I could have scaled this to the hundreds of millions, but my personal preference is to always scale to orders of magnitude that are multiples of 3: ones (10⁰), thousands (10³), millions (10⁶), billions (10⁹), etc.
Rather than recreate the code to scale your charts in every object of every application, here's a quick way you can get perfect scaling every time, without messing around with performance-heavy nested IF statements that repeatedly check whether a number if >=blah and <=blah. The following code is for Qlik applications, but is super simple to adapt to virtually any BI platform (even Excel!). Simply divide your chart values by the following denominator function:
Let's unpack what this does, starting with the inside and working our way to the outside:
- _______: this is simply a placeholder, which will be replaced by whatever parameter you want to scale to. Let's say for this example that the number we want to pass in is 14,125,985.
- log10(): the log10 function will return a logarithm value with a base of 10. In plain English, it tells you the exponent that you need to apply to the number 10 in order for it to result in the number you specified in the parameter. For our example number, 14,125,985, log10() returns approximately 7.15. That means that 10 to the power of 7.15 (ish) = 14,125,985.
- floor(__,3): this function rounds the number that's passed to it down to the nearest multiple of 3. As mentioned above, I prefer orders of magnitude that are multiples of 3. For our example, when we pass in 7.15, this function will return 6.
- pow(10,__): this is the inverse of the log10 function, and will return 10 to the power of the second parameter. When we pass in our 6, this function returns 1,000,000.
- rangemax(__,1): this is a fail-safe function, which will return whichever number is greater: the first parameter or 1. If something unexpected happened with any of the above steps (likely because the parameter in step 1 was invalid), we don't want our charts to break altogether. Instead, they will simply stop scaling; everything will be shown to the regular scale of ones. In our example, the fail-safe would not do anything since our number is valid. So this last step simply returns the same result as the previous step: 1,000,000.
Success! We now know that the number 14,125,985 should be scaled to the millions. In real-life situations, of course, you would not know in advance what number to pass in as the first parameter, you would want to make that dynamic. In Qlik, you can simply turn the above into a custom function using the power of variable parameterization (discussed at length here). Let's create a new variable, name it vDivideBy, and give is the following value:
I can call this function with the following syntax, where the middle part becomes the parameter:
As mentioned briefly above, a good rule of thumb is to scale to the smallest dimension value you have in a given chart. In Qlik syntax, that can be achieved with an aggr() function wrapped in a min(). For example, if I have a chart of sales by product, I could write:
And if I want to display some helper text in my chart to let users know the scale that I'm using, I can just wrap all of the above in a couple additional functions:
This technique has one important limitation, however: it will scale your entire chart to the same value. When you are dealing with graphical charts, that's absolutely a good thing—you would never want to divide one bar by a billion and another by a million, because the heights of the bars matter and you don't want to create a false comparison. But when you are displaying values in a text table, it can be very elegant to scale each row independently.
To do that, we can use the same basic concepts as above, but we cannot leverage the variable parameterization technique. Instead, the actual component functions themselves would be called directly in the table's expression:
Bonus tip: Don't forget to wrap the entire expression in a dual() function as shown here, so that Qlik understands the values are numbers and not text!
As always, all are welcome to download the full code behind the examples shown here and experiment for themselves. Enjoy, and if the tips in this article helped you, I would love to hear all about it in the comments below!
