Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Mahdoucch03
Contributor
Contributor

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

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

tableau.png

Labels (1)
9 Replies
edwin
Master II
Master II

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

Mahdoucch03
Contributor
Contributor
Author

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

edwin
Master II
Master II

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
Contributor
Contributor
Author

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

edwin
Master II
Master II

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
Contributor
Contributor
Author

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

Capture.PNG

edwin
Master II
Master II

can you pls post sample data?

edwin
Master II
Master II

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:

edwin_0-1665152521165.png

i checked this in xls and it works.

edwin
Master II
Master II

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;