topic Forecast Question in QlikView

Forecast Question

Tue, 20 Apr 2010 10:31:31 GMT

Hi everyone,

i got a litte problem a wanna make an small forecast, the data base is:

Employee, Date, Sales (CSV format)

The Employees are unique and the dates are over an peroid of 3 month, i just wanna get an forecast for the next month.

Forecast Question

Miguel_Angel_Baeyens — Tue, 20 Apr 2010 10:42:58 GMT

Hello,

You can create a calculated field in your load statement where you take the total, say, for the last 3 months, then divide it by the number of days for those 3 months and you will get a daily average. You then multiply that daily by the number of days for the next month and you get the forecast.

It this what you are looking for?

Regards.

Forecast Question

Tue, 20 Apr 2010 12:08:24 GMT

Yes it does help, thank you very much.

Forecast Question

Anonymous — Thu, 06 May 2010 12:23:28 GMT

Hi, This works when the forecast is only for 1 month, if forecase to be made for more than 1 month, how does it apply.

Forecast Question

Miguel_Angel_Baeyens — Thu, 06 May 2010 12:33:08 GMT

Hello Satish,

It depends on what type of forecast you want to do. My example above is a very simple projection based on actual sales. It could be as simple as multiplying the daily value by the number of days for the next three, four or the number of months you want using function

Day(MonthEnd(DateField))

so you will get actual number of days, or

networkdays(datefrom, dateto)

for net work days. You can either create a new table in your document with dates and values to forecast, link that table to the master calendar (or a new calendar) so you can choose all available dimensions to your forecast chart.

Regards.

Forecast Question

Anonymous — Thu, 06 May 2010 12:49:58 GMT

Hi Miguel ,

Thanks for the reply. My issue is

1. I have table which stores information like below

Month	Salary
01-Jan-10	1000
01-Feb-10	1500
01-Mar-10	1500
01-Apr-10	1200
01-May-10	1000
01-Jun-10	1230

Now i need to forecast my salary for rest of the year. I have generated the forecast month and number of days in each month. note that i'm doing all my work at expression level, as i require this information as flexible as possible.

so my expression is

if (len(Salary) <> 0, sum(Salary), (above(avg(All(column(4)/Days

))))

This being said, i do not want take only average of first six months to all the months for rest of the year. My each month should calculate Jun + Subsequent month(s) minus current month average for determining current month.

For example: to calculate July month my calculuation would be = (Sum(JantoJun Salary) / Sum of Noofdays in each month) this would be average day salary. Avg day salary * no of days in july.

Now similary for August, it should also be included July's salary for calculation. Do help how to do this. Thanks.

Forecast Question

Miguel_Angel_Baeyens — Thu, 06 May 2010 17:03:39 GMT

Satish,

If you use above function it will work always depending on the number of rows that exist in the table, so it's a good choice. I'd use something like (untested)

(rangesum(above(Sum(Salary), 0, RowNo())) / daynumberofyear(MonthEnd(Month))) * Day(MonthEnd(Month))

That expression will give you, for each row, the average forecast. Any new month added to the expression would automatically modify the values of rows above.

Is that what you are looking for?

Forecast Question

Mon, 22 Nov 2010 11:34:43 GMT

Hi Miguel,

I was also trying to do forecasting, using the expression u suggested above.

but the forecasted value is not getting included while calculating the next month forecast.

Not sure why.

when we do rangesum(above(sum(.........), its not considering the forecasted value and working on the values available in data set only.

Kindly Suggest!!

Regards,

Gaurav