topic Re: Adding Trendline with value 99% in QlikView

Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 12:06:20 GMT

Hello everbody,

I've been browsing through the questions about creating trendlines and find it difficult to implenment into my example.

Does someone have any idea on how I can add a trendline in the Bar_chart where the value should of the trendline is always 99% ?

If the data results drops below 99% it should be marked in red, if its 99% or higher it should be marked in green.

I've added a simple testfile so it's easyer to follow what I mean.

I hope anyone can help me please.

Kind regards,

Monique

Re: Adding Trendline with value 99%

Anonymous — Tue, 29 Apr 2014 12:10:48 GMT

Monuque,

If it is a fixed value (99%), what you need is not a trend line but a reference line. See Presentation tab. Define expression for your line as 0.99.

Regards,

Michael

Re: Adding Trendline with value 99%

maxgro — Tue, 29 Apr 2014 12:11:36 GMT

do you mean reference line, like that?

Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 12:12:06 GMT

Yes indeed

Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 12:25:03 GMT

Massimo,

Can you tell me how you did that please? I've been there to try it but I'm stuck on how to create the expression for the trendline.

Re: Adding Trendline with value 99%

maxgro — Tue, 29 Apr 2014 12:25:43 GMT

apply Michael answer to get a chart as my image

Re: Adding Trendline with value 99%

maxgro — Tue, 29 Apr 2014 12:33:26 GMT

this is the reference line in my previous post image

Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 12:34:58 GMT

Hello Michael,

I've found it but I notice that the trendline stays on the 100%, I would like it to be on the 99% value.

Because it so close to the 100% I tried to set the reference line on 0.90 and the trendline stayed on the 100%

So this is not exactly what I want Sorry

Re: Re: Adding Trendline with value 99%

Anonymous — Tue, 29 Apr 2014 12:40:50 GMT

See attached.
If you make your chart large enough, it is easy to see that the line is below 99.94%. Not 100% for sure.

Re: Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 12:47:53 GMT

You are absolutly wright, thank u so much.

Re: Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 13:57:46 GMT

Hello Michael,

Can I ask one more thing please, I would like to add a trendline with the average total %.

I tried to put it in an expression but the line doesn't show at all.

Can you tell me what's wrong with it please?

This is the expression I'm using:

Avg(Sum(Absolute)/Sum(Gecontroleerde_Colli))

Monique

Re: Re: Adding Trendline with value 99%

Anonymous — Tue, 29 Apr 2014 14:10:17 GMT

Monique,

You cannot use one aggregated function within another this way - you have sum() inside avg(). In situation like this, you must use aggr() function:
avg(aggr((Sum(Absolute)/Sum(Gecontroleerde_Colli)),<some fields here>))

I can't tell for sure what field(s) to use for aggregation in your case, it depends on what you want to see. See "help" for advanced aggregation.

Regards,

Michael

PS: It is a rather different question. I recommend to open a new discussion, so someone else can answer while I'm busy with my regular work

Re: Re: Adding Trendline with value 99%

amber2000 — Tue, 29 Apr 2014 16:06:39 GMT

Thank you for the response, I realy appreciate you trying to help me.

Kind regards,

Monique