Calculate Trendline

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
Henrik KlindtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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
Henrik KlindtAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
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
Henrik KlindtAuthor Commented:
I figured it out on your own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Algorithms

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.