Creating an explicit trend-line data column

[#1]

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!

[hector]

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 ¬¬'

[johnw · Accepted solution]

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.

[#4]

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

[#5]

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

[#6]

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

[Anonymous]

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