Believe it or not: 1

[hag99]

I need a clipper function or code that will do linear regression analysis. Need this to add to business plan forecasting program. Any direction will be of help.

 

[GriffMG]

Hi,

Do you mean a function that will take a series of points (from a table?) and calculate the y=ax+b formula (providing the 'straight line extrapolation' to y for any given value of x)?



Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Hi

Assuming that it is:

close all
clear

* to get Y=aX +b
Xavg = 0 		&& mean of X values
Yavg = 0		&& mean of Y values
XYprod = 0		&& mean of products of X and Y
Xsqr = 0		&& mean of X values squared
Ysqr = 0		&& mean of Y values squared
mcount = 0		&& number of values
aValue = 0		&& will be the 'a' part of the equation
bValue = 0		&& will be the 'b' part of the equation

use linear
go top
* process the table (could be an array)
do while .not. eof()
	* inc the counter
	mcount = mcount +1
	** sum in the values required
	Xavg = Xavg + X
	Yavg = Yavg + Y
	XYprod = XYprod + (X*Y)
	Xsqr = Xsqr + (X*X)
	Ysqr = Ysqr + (Y*Y)
	* get the next record
	skip
enddo
*assuming there is some data to work with
if mcount <> 0
	** finish calculating the means by dividing by the number of entries processed
	Xavg = Xavg / mcount
	Yavg = Yavg / mcount
	XYprod = XYprod / mcount
	Xsqr = Xsqr / mcount
	Ysqr = Ysqr / mcount
	** apply the 'least squares' approach to get the two values
	aValue = (XYprod - (Xavg * Yavg)) / (Xsqr  - (Xavg * Xavg))
	bValue = (Yavg - (aValue * Xavg))
endif


? "A = ", aValue
? "B = ", bValue

For the above I have a table (linear) with two numeric fields (X and Y). Any number of entries are possible, but if there are none you just get a zero answer.

It is possible that you coule get a divide by zero error when calculating aValue - you'll need to handle that.




Regards

Griff
Keep [Smile]ing

 

[hag99]

Grill:
Thanks for the very quick response.
Frankly I don't know exactly how to do what I want but here is the problem:
I don't want to plot on a graph. I want to take two to three years of a companys actual total 12 months sales use linear regression and predict the next years sales.
Say year 1 = 1,000,000
year 2 = 1,600,000
year 3 = 1,750,000

What would be the coefficient to apply to each months sales and what would be year 4 sales in total?
Need the code to solve the problem.

Thanks sooo much.
Harvey

 

[GriffMG]

Hi Harvey,

The code I've given will do that for you, but here is an array version which will yield the result you want

DECLARE XCoords[3],YCoords[3]
XCoords[1] = 1000000
XCoords[2] = 2
XCoords[3] = 3
YCoords[1] = 1000000
YCoords[2] = 1600000
YCoords[3] = 1750000

? ExtraPol(XCoords,YCoords,4)


FUNCTION Extrapol
	PARAMETERS arryX,arryY,NewXValue
	PRIVATE i,NoElem,RetValue
	PRIVATE Xavg  		&& mean of X values
	PRIVATE Yavg 		&& mean of Y values
	PRIVATE XYprod 		&& mean of products of X and Y
	PRIVATE Xsqr 		&& mean of X values squared
	PRIVATE Ysqr 		&& mean of Y values squared
	PRIVATE mcount 		&& number of values
	PRIVATE aValue 		&& will be the 'a' part of the equation
	PRIVATE bValue 		&& will be the 'b' part of the equation

	* to get Y=aX +b
	Xavg = 0 		&& mean of X values
	Yavg = 0		&& mean of Y values
	XYprod = 0		&& mean of products of X and Y
	Xsqr = 0		&& mean of X values squared
	Ysqr = 0		&& mean of Y values squared
	aValue = 0		&& will be the 'a' part of the equation
	bValue = 0		&& will be the 'b' part of the equation
	retValue = 0
	noElem = LEN(arryX)
	FOR i = 1 TO noElem
		** sum in the values required
		Xavg = Xavg + arryX[i]
		Yavg = Yavg + arryY[i]
		XYprod = XYprod + (arryX[i]*arryY[i])
		Xsqr = Xsqr + (arryX[i]*arryX[i])
		Ysqr = Ysqr + (arryY[i]*arryY[i])
	NEXT

	*assuming there is some data to work with
	IF noElem <> 0
		** finish calculating the means by dividing by the number of entries processed
		Xavg = Xavg / noElem
		Yavg = Yavg / noElem
		XYprod = XYprod / noElem
		Xsqr = Xsqr / noElem
		Ysqr = Ysqr / noElem
		** apply the 'least squares' approach to get the two values
		aValue = (XYprod - (Xavg * Yavg)) / (Xsqr  - (Xavg * Xavg))
		bValue = (Yavg - (aValue * Xavg))
		** now calculate the y value for the new X
		retValue = (aValue * NewXValue) + bValue
	ENDIF

	RETURN(retValue)

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Oops - that value for XCoords[1] should have been a 1 not a million!

B-)

Regards

Griff
Keep [Smile]ing

 

[hag99]

Griff:

Thanks a million. I'll give it a try this evening. BTW are you familiar wit fivewin. My app is in fw192 and works great. Need to get to 32 bits. Any thoughts?

 

[GriffMG]

Honestly, I'd go VFP - it's the dbs

Alaska is ok, and not bad for a quick conversion - but the finished products look clunky and dated, to me.

No exp of xHarbour or FiveWin.

Incidently, I wrote your function in VFP 6 first (db version) and then converted to Clipper 5 for the array one.

Very pleased to help,

Martin

Regards

Griff
Keep [Smile]ing

 

[hag99]

VFP how is it in a conversion?

 

[GriffMG]

Nah, that is not so good.

For a windows conversion Alaska xBase is about 85% perfect - but it's not quite 100%. There are differences in the way it handles text files etc.

VFP is for 'new builds' and complete rewrites - the nice thing is converting the tricky bits (for me) the code elements is easy - the visual bits are way different.

It also copes with long file names and long variable names that start to make sense...

i.e. Extrapolate() is ok, or even GetMeALinearProgresssionForThisSeries()

B-)



Regards

Griff
Keep [Smile]ing

 

[hag99]

Griff:
Works like a charm...Thanks for the help.

 

[GriffMG]

Sweet

Glad to help

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

BTW

Interpolaton is pretty reliable - you are working in the proven set of data.

Extrapolation is not so good - you are working from the basis of proven results, into unexplored territory.

Good luck


Regards

Griff
Keep [Smile]ing