How to predect the values of the next year from the last two years

[Mahdoucch03]

Hi friends,i want to predect the values of the next year . 

[edwin]

you need to specify what the logic or business rule is for the predicted value so that someone can help you

[Mahdoucch03]

I want to predict sales amounts for next year based on the last two years

[edwin]

but there must be business rules to do that like is it average?  is it min? is it max?  or is there a formula?

[Mahdoucch03]

ah okay the formula is (avg of 3 last months + avg of the same month in the last year )/2

[edwin]

is your data monthly?  if so, why do you average the same month last year?  shouldnt it just be a single number?  if it is not a single number averaging it will give you a very small number. 
can you post sample data?

[Mahdoucch03]

I want to predict the value of this month and the next two months nov and dec 

[edwin]

can you pls post sample data?

[edwin]

try this:

data:
load addmonths(MonthStart(today()), -IterNo()+1) as Month, floor(rand()*1000) as Amount,  'Actual' as Prediction
while iterno()<=34;
load 1 AutoGenerate(1);

Concatenate(data)
load addmonths(MonthStart(today()), iterNo()) as Month, 'Prediction' as Prediction
while iterno()<=2;
load 1 AutoGenerate(1);


NoConcatenate
tmpData:
load * Resident data where Prediction='Prediction';

inner join (tmpData)
load Month as tmpMonth, Amount as tmpAmount
Resident data;

NoConcatenate
tmp2Data:
load *,
if(tmpMonth=AddMonths(Month,-12),'Same month prior year', 'Prior 3 months') as Flag
Resident tmpData
where tmpMonth<Month and tmpMonth>=AddMonths(Month,-3)
or tmpMonth=AddMonths(Month,-12);


NoConcatenate
aggrData:
load Month, Prediction, Flag,
avg(tmpAmount) as AvgAmount
Resident tmp2Data
group by Month, Prediction, Flag;

NoConcatenate
newData:
load * Resident data where Prediction='Actual';

Concatenate (newData)
load Month, Prediction, sum(AvgAmount)/2 as Amount
Resident aggrData
group by Month, Prediction;



drop table data, tmpData, tmp2Data, aggrData;

this will at the end add the predictions in the Amount column and flag it appropriately in the Prediction column.  this is a sample run:

i checked this in xls and it works.

[edwin]

here is a simpler version:

data:
load addmonths(MonthStart(today()), -IterNo()+1) as Month, floor(rand()*1000) as Amount,  'Actual' as Prediction
while iterno()<=34;
load 1 AutoGenerate(1);


NoConcatenate
tmpData:
load addmonths(MonthStart(today()), iterNo()) as Month, 'Prediction' as Prediction
while iterno()<=2;
load 1 AutoGenerate(1);

inner join (tmpData)
load Month as tmpMonth, Amount as tmpAmount
Resident data;

NoConcatenate
tmp2Data:
load *,
if(tmpMonth=AddMonths(Month,-12),'Same month prior year', 'Prior 3 months') as Flag
Resident tmpData
where tmpMonth<Month and tmpMonth>=AddMonths(Month,-3)
or tmpMonth=AddMonths(Month,-12);


NoConcatenate
aggrData:
load Month, Prediction, Flag,
avg(tmpAmount) as AvgAmount
Resident tmp2Data
group by Month, Prediction, Flag;

Concatenate (data)
load Month, Prediction, sum(AvgAmount)/2 as Amount
Resident aggrData
group by Month, Prediction;