?
Solved

Calculate Trendline

Posted on 2014-08-21
4
Medium Priority
?
342 Views
Last Modified: 2014-08-29
Hi
I need to calculate the trend line like the one in the attached document. In the document you can see the result in excel and that is what i have to end up with.

This is the data I got to calculate the trendline.

Year      Month      Week      Day            Open      Closed
2014      7      29      16            13      20
2014      7      29      17            28      29
2014      7      29      18            21      24
2014      7      30      21            24      30
2014      7      30      22            23      22
2014      7      30      23            25      40
2014      7      30      24            17      22
2014      7      30      25            24      31
2014      7      31      28            17      22
2014      7      31      29            12      16
2014      7      31      30            24      22
2014      7      31      31            21      25
2014      8      31      1            28      23
2014      8      32      4            20      24
2014      8      32      5            18      23
2014      8      32      6            22      30
2014      8      32      7            23      26
2014      8      32      8            16      12
2014      8      33      11            15      17
2014      8      33      12            14      24
2014      8      33      13            24      19
2014      8      33      14            12      25
2014      8      33      15            16      14
2014      8      34      18            21      30
2014      8      34      19            9      28
trendlinetest.xlsx
0
Comment
Question by:Henrik Klindt
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40277698
try the following

For a set of data points (x, y), this algorithm can be used to fit the data to any of the following curves:

1. Straight line (linear regresion); y = A + b*x
2. Exponential curve; y = A*EXP(b*x); nb a > 0
3. Logarithmic curve; y = A + b*LN(x)
4. Power curve; y = A*x^b; nb a > 0

The coefficient of determination is R2 (how well does the curve fit)
-- Prepare test data
CREATE TABLE	cf
		(
			x decimal(38, 10),
			y decimal(38, 10)
		) 

-- Calculate Linear regression
INSERT	cf
SELECT	40.5, 104.5 UNION ALL
SELECT	38.6, 102 UNION ALL
SELECT	37.9, 100 UNION ALL
SELECT	36.2, 97.5 UNION ALL
SELECT	35.1, 95.5 UNION ALL
SELECT	34.6, 94

SELECT	'Linear regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Exponential regression
DELETE
FROM	cf

INSERT	cf
SELECT	.72, 2.16 UNION ALL
SELECT	1.31, 1.61 UNION ALL
SELECT	1.95, 1.16 UNION ALL
SELECT	2.58, .85 UNION ALL
SELECT	3.14, .5

SELECT	'Exponential regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Logarithmic regression
DELETE
FROM	cf

INSERT	cf
SELECT	3, 1.5 UNION ALL
SELECT	4, 9.3 UNION ALL
SELECT	6, 23.4 UNION ALL
SELECT	10, 45.8 UNION ALL
SELECT	12, 60.1

SELECT	'Logarithmic regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Power regression
DELETE
FROM	cf

INSERT	cf
SELECT	10, .95 UNION ALL
SELECT	12, 1.05 UNION ALL
SELECT	15, 1.25 UNION ALL
SELECT	17, 1.41 UNION ALL
SELECT	20, 1.73 UNION ALL
SELECT	22, 2 UNION ALL
SELECT	25, 2.53 UNION ALL
SELECT	27, 2.98 UNION ALL
SELECT	30, 3.85 UNION ALL
SELECT	32, 4.59 UNION ALL
SELECT	35, 6.02

SELECT	'Power regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

DROP TABLE cf

Open in new window


source: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311
0
 

Accepted Solution

by:
Henrik Klindt earned 0 total points
ID: 40278442
Hi Brian
I can't figure out how to use your solution.

In this topic
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_26713647.html
I found what I am looking for. These two lines returns regression and startpoint

(Avg(row * 1.000 * opened) - Avg(row * 1.000) * Avg(opened * 1.000)) / VarP(row * 1.000) AS HaeldningOpen
,Avg(opened * 1.000) - ((Avg(row * 1.000 * opened * 1.000) - Avg(row * 1.000) * Avg(opened * 1.000)) / VarP(row * 1.000)) * Avg(row * 1.000) AS StartOpen
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40278880
I am glad you found what you were looking for.  Just request that the topic be deleted or closed without awarding points since you figured it out on your own.

Good Luck
0
 

Author Closing Comment

by:Henrik Klindt
ID: 40292155
I figured it out on your own.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question