topic Re: How can I create predictive or forecasting Analysis in QlikView

How can I create predictive or forecasting Analysis

Fri, 20 Feb 2015 01:04:46 GMT

Hi all,

Is it possible to create forecast analysis for 2015 with QV ?

Sample Data:

LOAD * INLINE [

Year, Month, Value

2014, 1, 100

2014, 2, 500

...

2015, 1, 50

2015, 2, 100

];

How qv can create others months 3,4,5,6,7,8,9,10,11,12 for the forecasting 2015 ?

Thanks for your help.

Re: How can I create predictive or forecasting Analysis

sunny_talwar — Fri, 20 Feb 2015 03:26:04 GMT

QlikView can definitely create Future values for Month and Year, but you need to have some basis for forecasting your Value? Do you have some trend that you would want to forecast?

Best,

Re: How can I create predictive or forecasting Analysis

Sat, 21 Feb 2015 09:47:43 GMT

Hi Sunindia,

Thank you for your your response.

With my sample, is it possible to create a trend forecasting or it is not possible with the actual data. I have a value for all months in 2014 and only for jan and feb of 2015 year. My idea is to imagine this value at the end of year. (dec. 2015). I don't know how can I do that ?

What are the parameters missing into my model to achieve that ?

Thanks for your help.

Re: How can I create predictive or forecasting Analysis

Sat, 21 Feb 2015 10:45:32 GMT

Hi,

Forecasting is not an out of the box feature in Qlikview. You need to create the function with the assumptions you need for the forecast. For example, you might want a simple forecast that takes the same month last year or a more sophisticated one with a linear regression to extrapolate the missing data. All depends on the conditions you need.

You should read Forecasting in Qlikview to learn how these solutions can be implemented.

Hope this helps.

Kind regards,

Nuno

Re: How can I create predictive or forecasting Analysis

sunny_talwar — Sat, 21 Feb 2015 12:18:30 GMT

So the dates have been created till 31/12/2015, but we need to know how would you forecast Active and Termination? Right now I am just using the the last available numbers from 02/16/2015 to carry them forward for all the dates till 2015. But this may not be the correct forecasting. Is there a specific behavior that it follows? Like cyclical behavior or always increasing? Until we know how we want to forecast, this will give us garbage.

PFA the application.

HTH

Best,

Re: How can I create predictive or forecasting Analysis

Anonymous — Sat, 21 Feb 2015 17:07:05 GMT

Hi,

Forecasting- Is to predict what will be the best margin on sales depending on no of products sold.

For eg : If you have sold for 5 products will fetch you 100 pounds.

10 products - 180 pounds

20 products - 320 pounds

Now if you want to know with which quantity of products you get the highest margin of profit?

You make calculations to find out profit based on selected quantity respectively.

These calculations which are done are termed as Forecasting.

in your example:

Sample Data:

LOAD * INLINE [

Year, Month, Value

2014, 1, 100

2014, 2, 500

...

2015, 1, 50

2015, 2, 100

];

You can create a calculated field in your load statement where you take the total, say, for the last 14 months(from Jan to 2015 feb), then divide it by the number of days for 14 months and you will get a daily average. You then multiply that daily by the number of days for each month (i.e next 10 month) and you get the forecast for others months 3,4,5,6,7,8,9,10,11,12 for the forecasting 2015 .

See below link:

http://community.qlik.com/docs/DOC-3555

Regards

Neetha

Re: How can I create predictive or forecasting Analysis

Sat, 21 Feb 2015 18:14:45 GMT

Sunindia,

The difficulty is Active can increase or decrease in time like Termination.

Active are employees who start the company ( can varied)

Termination are employees who left the company (can varied)

Re: How can I create predictive or forecasting Analysis

Sun, 22 Feb 2015 13:16:46 GMT

Neetha P,

Thanks for your response, interesting!

But How can I create months (3,4,5,6,7,8,9,10,11,12 for 2015) in the model with the Inputlfield Active and Termination ?

he solution with inputfield and InputSum is more flexible but how can I create 12 months for 2015 ?

See attachment.

Thanks for your help.

Re: How can I create predictive or forecasting Analysis

Anonymous — Sun, 22 Feb 2015 13:32:24 GMT

Hi Judikael,

Please try below code for generating calendar:

LET vTodaysDate = num((MakeDate(2015, 2, 28)));
LET vStartDate = num(AddMonths(YearStart($(vTodaysDate)),-2*12));
LET vEndDate = num(YearEnd($(vTodaysDate)));
LET NumberOfDays = num(YearEnd($(vTodaysDate))) - $(vStartDate) + 1;

Table:
LOAD DISTINCT
Year(Date) as Year,
Month(Date) as Month,
Date(MonthStart(Date), 'MMMYY') as MonthYear,
if(monthstart(Date) <= $(vTodaysDate), 1, 0) as _History,
'Q' & Ceil(Month(Date)/3) as Quarter,
Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), Year(Date) & Ceil(Month(Date)/3)) as QtrYear,
Week(Date) as Week,
Weekstart(Date) as Weekstart,
Weekend(Date) as Weekend
;
Load
Date($(vEndDate) - RecNo() +1) as Date
AutoGenerate($(NumberOfDays));

Regards

Neetha

Re: How can I create predictive or forecasting Analysis

Sun, 22 Feb 2015 17:53:11 GMT

Neetha,

Thanks for your excellent calendar but how can I have 12 months for 2015 in straight table ?

Thank for your help.

Re: How can I create predictive or forecasting Analysis

Anonymous — Sun, 22 Feb 2015 19:13:39 GMT

Hi judikael,

Please select month dimension in straight chart.

Regards

Neetha

Re: How can I create predictive or forecasting Analysis

Sun, 22 Feb 2015 19:37:19 GMT

Neetha,

I select the dimension but when I select 2015 year I only can see Jan and Feb not 12 months.

Thanks.

Re: How can I create predictive or forecasting Analysis

Anonymous — Sun, 22 Feb 2015 19:42:24 GMT

Hi Judikael,

Once you populate data for these months in 2015 you can see months in straight table for selection of year 2015

Re: How can I create predictive or forecasting Analysis

Anonymous — Sun, 22 Feb 2015 19:53:57 GMT

Hi,

Use below logic in script to populate data for req months:

Load Year,Month,
If( IsNull( sales ), Peek( Sales ),Sales ) as Sales
Resident Sales
Order By Year,Month;

Re: How can I create predictive or forecasting Analysis

Sun, 22 Feb 2015 23:28:21 GMT

Thank you very much Neetha P. for your help.

Re: How can I create predictive or forecasting Analysis

Wed, 25 Feb 2015 04:10:29 GMT

Hi Neetha,

I tried to use the forecasting with the method Inputfiled. It work fine

I would like to test your suggestion to compare but I don't know how can I realize it.

" You can create a calculated field in your load statement where you take the total, say, for the last 14 months(from Jan to 2015 feb), then divide it by the number of days for 14 months and you will get a daily average. You then multiply that daily by the number of days for each month (i.e next 10 month) and you get the forecast for others months 3,4,5,6,7,8,9,10,11,12 for the forecasting 2015 ".

The goal is to have Active for next 10 months (3,4,5,6,7,8,9,10,11,12 ) ,Termination for next 10 months (3,4,5,6,7,8,9,10,11,12 )

and finally calculate the To for next 10 months (3,4,5,6,7,8,9,10,11,12 ) where To is Sum(Termination) / ( Sum(Active) / Count(distinct CalendarDate) ).

Thanks for your help.

Re: How can I create predictive or forecasting Analysis

Anonymous — Thu, 26 Feb 2015 11:23:48 GMT

Hi Judikael,

Can you copy paste your code here as i am using personnel edition.

attach sample data please.

Regards

Neetha

Re: How can I create predictive or forecasting Analysis

Fri, 27 Feb 2015 14:33:21 GMT

Hi Neetha,

TEMP:
LOAD * INLINE [
Year,MonthNum,CalendarDate,Active,Termination
2014,1,41641,5 435,0
2014,1,41643,5 449,0
2014,1,41644,5 449,0
2014,1,41650,5 515,0
2014,1,41651,5 515,0
2014,1,41657,5 552,0
2014,1,41658,5 552,0
2014,1,41664,5 633,0
2014,1,41665,5 635,0
2014,1,41640,5 423,1
2014,1,41655,5 550,1
2014,1,41666,5 644,1
2014,1,41669,5 683,1
2014,1,41642,5 449,2
2014,1,41645,5 454,2
2014,1,41647,5 489,2
2014,1,41648,5 508,2
2014,1,41649,5 515,2
2014,1,41654,5 551,2
2014,1,41656,5 552,2
2014,1,41661,5 601,2
2014,1,41667,5 655,2
2014,1,41668,5 671,2
2014,1,41652,5 527,3
2014,1,41662,5 620,3
2014,1,41663,5 633,3
2014,1,41646,5 471,4
2014,1,41653,5 540,4
2014,1,41660,5 583,4
2014,1,41659,5 564,5
2014,1,41670,5 632,59
2015,1,42022,7 136,0
2015,1,42029,7 279,0
2015,1,42030,7 318,0
2015,1,42006,6 932,1
2015,1,42008,6 927,1
2015,1,42009,6 928,1
2015,1,42016,6 968,1
2015,1,42023,7 182,1
2015,1,42024,7 237,1
2015,1,42015,6 934,2
2015,1,42017,7 046,2
2015,1,42018,7 092,2
2015,1,42028,7 279,2
2015,1,42033,7 428,2
2015,1,42014,6 936,3
2015,1,42032,7 380,3
2015,1,42005,6 933,4
2015,1,42007,6 928,4
2015,1,42011,6 937,4
2015,1,42012,6 936,4
2015,1,42013,6 939,4
2015,1,42021,7 136,4
2015,1,42027,7 281,4
2015,1,42031,7 348,4
2015,1,42019,7 131,5
2015,1,42020,7 140,5
2015,1,42026,7 283,5
2015,1,42010,6 931,6
2015,1,42025,7 282,6
2015,1,42034,7 433,9
2015,1,42035,7 372,61];

LOAD
Year,
Num(Sum(Active) /30 ,'# ##0') as TotalActive,
Num(Sum(Termination) / 30,'# ##0') as TotalTermination
RESIDENT TEMP
GROUP BY Year;

DROP TABLE TEMP;

See attachment,

Thanks,

Re: How can I create predictive or forecasting Analysis

Tue, 03 Mar 2015 19:49:28 GMT

Hi Neetha,

Have you look around my sample data ?

Thanks