Trick #1: Better Grid Lines

So you create a bar chart in QlikView...

You go to the chart's Presentation tab and enable the grid lines. The chart renders, 3 grid lines appear and you smile.

Life is good.

Then you create another bar chart using the same methodology but maybe slightly different dimensions or measures. Except this time, when the chart renders, it has 7 grid lines. Huh??

You never told it to have 7 grid lines, QlikView just decided that 7 was the right number for this graph.

Okay, it is what it is. Then you create a third bar chart and this one has 15 grid lines!!!

Again, you never told QlikView how many to have. In fact, I don't think that's even an (obvious) option for doing so.

So you hold your breath and show your charts to the client. They're happy... except... they want every chart to have exactly 5 grid lines. (True story.)

Sigh.

You shuffle back to your desk and poke around. That's when you discover (or remember) there's a Static Step parameter within the chart properties. You can use that!

But what do you put in there?  Here are your options:

1) You could just eye-ball the chart for the max value (tallest bar), add a little fudge to it and then divide that value by 5 and put the result in the Static Step box.  DO NOT DO THIS!  Eventually the day will come when the data in the chart exceeds your "guestimate" and then you'll look as silly as your chart.

2) If you're thinking that, since you can put an expression in the Static Step box, you should copy your chart's Measure expression in there, surround it with a max() function and then divide that by 5...

=max( your_expression)/5

...you're actually getting warm.

But what happens when =max( your_expression) produces a number like 8192 resulting in a value of 8192/5 or 1638.4?  Do you really think your clients will want to see multiples of 1638.4 on their grid lines? Really??? No, of course not.

What you need is something that will thrill your clients; after all, that's what it's all about right?

3) Suppose we round the above expression upwards? But not just upwards! Upwards to the next multiple of the power of 10.  Did your head just explode?

That's right. The next multiple of a power of 10. So, for example, if you have 8192, you want to round up to 9000. That's a nice round number. If we just did a multiple of 10, then 8192 would round up to 8200. That's a round number too, but not as nice as 9000. Don't you agree?

So what's an easy way to determine the next multiple of a power of 10, you ask?

Behold...

=ceil( max( your_expression), pow( 10, len( max( your_expression)) -1)))/5

Just replace the your_expression part with your chart's actual Measure expression.  If your client would rather have some other number of grid lines, simply replace the 5 in the above expression with whatever number they desire.

Note: If you employ any funky calculated dimensions or set analysis in your expression you might have to wrap your expression in an aggr() function like this first:

aggr( your_expression, your_dimension)

TRUTH BE TOLD: Even with this super cool step expression, QlikView may or may not display the top-most grid line. If you want to insure that the max line is always displayed, copy the step expression into the Static Max parameter (but remove the divisor first!).

Okay, so that's my first QlikView trick. Hope you enjoy it as much as I enjoyed sharing it with you.