topic Creating an explicit trend-line data column in QlikView

Creating an explicit trend-line data column

Thu, 06 Jan 2011 18:10:08 GMT

I would like to have an explicit trend-line data column. I know that I can automatically have a trend line on a graph (through Expressions -> Trendlines), but what I would like to explicitly have a column with this data in. I will give an example.

Let's say there are 3 columns which I want: Date, Performance (in seconds), Trend (in seconds) - a calculated field, which let's say looks as follows:

25/01/2011 5.2 5.0

26/01/2011 6.1 6.0

27/01/2011 7.4 7.0

My guess is that functions like LINEST_M will help me get what I want, but I'm too novice to know how to do this.

Thanks!

Creating an explicit trend-line data column

hector — Thu, 06 Jan 2011 18:33:17 GMT

Hi, I'm not following you so much, but linest_m will return the same value of the calculated trendline in the chart

if you want to draw your personal trendline, just add the corresponding expression to the chart, i guess it will be a combo chart

Check the attachment and tell us if this is what you want

Rgds

Ps. Change the extension png for qvw, i'm still dealing with this issue, i'm unable to upload any QVW file ¬¬'

Creating an explicit trend-line data column

johnw — Fri, 07 Jan 2011 03:05:57 GMT


Hugh wrote:My guess is that functions like LINEST_M will help me get what I want

Good guess, but yes, how to use it is hardly obvious. The basic format is this:

X*linest_m(total aggr(Y expression,X))
+linest_b(total aggr(Y expression,X))

So I believe for you:

Dimension = Date
Expression 1 = Your Performance Expression
Expression 2 = Date*linest_m(total aggr(Your Performance Expression,Date))
+linest_b(total aggr(Your Performance Expression,Date))

Attached is an example. It's not your data, but same basic idea.

I tend to prefer explicit trend lines like this, among other things because you can mouse over a point on the trend line and see the value at that point. It also gives you more control - the automatic trend line just trends based on what the chart displays, while you can make your explicit trend line follow the underlying data. For instance, we might have a dynamic class interval. The automatic trend line can change position and slope as we adjust our interval. An explicit trend line can be made fixed, based on the unclassed underlying data.

Creating an explicit trend-line data column

Fri, 07 Jan 2011 11:35:30 GMT

Great, thanks for the quick reply. That does it!

Creating an explicit trend-line data column

Wed, 27 Jul 2011 12:48:18 GMT

. If i have 2 lines and i use this function, is it possible to calculate a date when one line touches the other line?

Re: Creating an explicit trend-line data column

Wed, 14 Jan 2015 21:27:49 GMT

I have nearly the same question. How can I calculate the day when a trend line is projected to intersect a Reference line.

Re: Creating an explicit trend-line data column

Anonymous — Fri, 02 Jun 2017 05:31:07 GMT

Thanks John and hugh..Your solution has helped me too. Post has been very helpful