topic Set reference line in scatter chart according to scale in QlikView

Set reference line in scatter chart according to scale

Thu, 17 Nov 2011 14:55:48 GMT

Hi,

since it's easier to explain with an example, the attached file is what I would like to obtain.

So I would like to be able to set the position of a reference line according to the scale of the axis.

I see you coming, telling me it's easy! Well, if I want to set it from the maximum value in my chart (as you can set the max scale), yes. But I would like to position it at the middle of the axis itself, without consideration on the value of the points.

So if as in the example, my axis goes from 0 to 400, I want to set it at 400 / 2. But automatically, since the max can change.

Hope the explaination is good enough, many thanks in advance for your help!

benetche

Set reference line in scatter chart according to scale

Thu, 17 Nov 2011 20:50:29 GMT

Hi Benetche

There is no way to reference the Max from within a chart in qlikview (as far as I know). So the solution here would be to set the max manually, and then use the same function to set your reference line.

To set a "manual Max" you will need to max using the aggregate function over the dimension, containing the same expression for that axis. For example if your Y expression is "=sum(Amount)" and your dimension is "Month" then your function would look like:

=max(aggr(sum(Amount),Month))

I also X by 1.1 or some arbitrary amount so that the greatest figures are not exactly on the edge of the chart. I also use the ceil function to round the max to the nearest figure:

=ceil(max(aggr(sum(Amount),[Activity Month])),1000)

Each reference line would use this formula, divided by 2.

In addition to using a reference line, you could aslo use gridlines and set the step to [the maximum for that axis]/2. This would add flexibility if you wanted to specify a different number of lines in the future.

Hope this helps,

Regards,

Erica

Set reference line in scatter chart according to scale

Fri, 18 Nov 2011 09:00:21 GMT

Ok,

I was using kind of the same expression, but wasn't sure whether there was a better solution or no. But the use of ceil is pretty smart, at least if you know all the ranges you'll go through.

I finally will only use the "max(aggr..." expression, multiplying by a 1.15 coefficient and of course divide by 2 for the reference line.

Thx Erica.