Execute Linear regression using Macro's

[Former Member]

Hi APO Guru's,

I am trying to simulate Linear regression using macros , and i have come across the below 2 macro functions which could be used for linear regression.

TREND() =  TREND( X1 ; ... ; Xn ; Y1 ; ... ; Yn ) returns the trend B in a linear regression line Y = A + BX for a series of observed pairs of values

CONSTANT() = CONSTANT( X1 ; ... ; Xn ; Y1 ; ... ; Yn ) returns the constant A of a linear regression line Y = A + BX

But i am not quite sure how to make use of these macros to good use , without the help of forecasting profiles.

My condition is as given below :

Sales data is at week level , I would like to Execute linear regression macros in Month Data View.
Such that the Spike in week sales are eliminated

Please advise
Anish

[Former Member · Accepted solution]

Hi Rajesh,

Thank you for your quick reponse. I have few clarification on your suggestion.

In my case i don't have Independent variable like Price as you have suggested , Could i use time as a Independent variable i.e W22....W27 if so how can this be expressed in a macro.

In your example of Step 1, there is mention of AREA of KF( Sales History). Does area mean array of sales or is it a syntax to be used.

Regards
Anish

[rajkj · Accepted solution]

Anish,

1. Independent variable - Price - is the variable X in Y = A + BX. To use linear regression, you must have at least one independent variable with historical known values and projected future values so as to find out the good fit and derive the dependent variable future values.

2. Time periods can only be used in passing values to the macro functions i.e. values of your dependent and independent variables for the selected horizon.

3. AREA is a macro element like array. It allows you to consider multiple values of your key figure at a time.

Thanks,

Rajesh

[rajkj]

Hi Anish,

Consider the following example.

Key Fig	W22	W23	W24	W25	W26	W27	W28	W29
Price per Unit	1.25	1.30	1.31	1.29	1.28	1.26	1.27	1.29
Sales History	1500	1400	1370	1450	1470	1480
Projected Sales							1475	1410

Assume the price per unit is an independent variable and impacts the sales volume. The week starts from Sunday and ends with Saturday. The current week is W27. The objective is to find out the relationship between past sales and price variations and apply that intelligence to find out the projected sales with future price variations.

Step 1: Initialize variable (1 iteration)

LAYOUTVARIABLE_SET( 'A' ; CONSTANT(AREA of PRICE PER UNIT[W22 ... W27] ; AREA of Sales History[W22 .. W27] ) )

LAYOUTVARIABLE_SET( 'B' ; TREND(AREA of PRICE PER UNIT[W22 ... W27] ; AREA of Sales History[W22 .. W27] ) )

Step 2: Derive projected sales (Future iterations i.e. from W28 onwards)

Projected Sales = EVAL( LAYOUTVAR_VALUE( 'A' ) + ( LAYOUTVAR_VALUE( 'B' ) *  Row Price Per Unit (W28) ) )

The same applies even to monthly buckets.

Thanks,
Rajesh