topic Re: Unwanted Forced 0 in QlikView

Unwanted Forced 0

Tue, 20 Sep 2016 18:58:50 GMT

Hi all!

I'm making a chart with a trend line (monthly history) and two average lines based on calculations (using AVG TOTAL instead of the available average trend line). I have unchecked 'Forced 0' for all three lines under the Axes tab but my y-axis still starts at 0. Because the chart is intending to be dynamic, I don't want to set a static min/max. As an example, one set of data ranges from 10-35.

Any ideas for how to get the chart's y-axis min to change?

Thanks so much for your help!

Shannon

Re: Unwanted Forced 0

swuehl — Tue, 20 Sep 2016 19:16:21 GMT

Your 'static' min / max don't need to be constant, you can use expressions, hence stay dynamic.

But maybe the solution is much more simple, could you post a small sample QVW?

Re: Unwanted Forced 0

Tue, 20 Sep 2016 19:30:03 GMT

It might take a while to post a sample QVW because of my dataset - not sure it can be done with my existing data. But the trend line expression is below if that is any help.

Why do I need to set static min/max at all if Forced 0 is unchecked?

Thanks, Stefan!

=if(invert = '1',

sum({<frequency = {'M'},universe= {'All'}>}(1/Value)),

sum({<frequency = {'M'},universe = {'All'}>}Value))

Re: Unwanted Forced 0

swuehl — Tue, 20 Sep 2016 19:55:40 GMT

Could you post all dimensions and expressions you are using?

I thought you mentioned something with Avg(TOTAL ...) as trendline, your above expression seems pretty different to that.

Re: Unwanted Forced 0

Tue, 20 Sep 2016 20:02:55 GMT

Of course! To be clear, the above expression is the trend line and the two below are the average lines which use AVG(TOTAL()). 'All' isn't really all universes in case that is point of confusion.

=if(invert = '1',
avg(TOTAL {<frequency = {'M'},universe = {'All'}>}(1/Value)),
avg(TOTAL {<frequency = {'M'},universe = {'All'}>}Value))

=if(invert = '1',
avg(TOTAL {<frequency = {'M'},universe_name = {'All','US','Europe'}>}(1/Value)),
avg(TOTAL {<frequency = {'M'},universe_name = {'All','US','Europe'}>}Value))

Thanks!

Re: Unwanted Forced 0

swuehl — Tue, 20 Sep 2016 20:09:52 GMT

If you transform your chart to a straight table, do some expression cells get close to zero?

Could you post some screenshots?

Re: Unwanted Forced 0

Wed, 21 Sep 2016 14:33:54 GMT

I've found an alternate solution.

By adding the average expressions as reference lines, the y-axis becomes dynamic without having to add an expression for static min/max. To add a reference line, go to the presentation tab in chart properties and click 'Add' in the 'Reference Lines' box. Make sure to click 'Primary Y' when adding the reference line.

Thanks for looking into my question, swuehl !