topic Re: Negative growth forecast error in QlikView

Negative growth forecast error

didierodayo — Tue, 28 Mar 2017 23:52:58 GMT

Hello I need some help to fix the issue below.

I have a sale data where a maximum sale limit of the product has been set. Based on historical sale I am trying to forecast when the sale will reach the sale limit. My script below seem to be working but I notice when there is a negative sale growth the forecast date is in the past.

Note: the date are Sunday dates (I don't think it matters).

I have attached the data and qvw. files below.

Sales:
LOAD Date,
Product,
[Sale limit],
Sale
FROM
forecasting.xls
(biff, embedded labels, table is Sheet1$);

SalesLimitHit:
LOAD
Product,
Date(([Sale limit]-LINEST_B(Sale, Date))/LINEST_M(Sale, Date),'DD/MM/YYYY') as [Limit Hit Date]
Resident Sales
Group By
Product,
[Sale limit];

Re: Negative growth forecast error

sunny_talwar — Wed, 29 Mar 2017 02:01:40 GMT

If the Sale has declined, how will you reach Sale limit in the future using regression analysis? The line is going to be downward sloping and you will never get a future date... what is the expectation here?

Re: Negative growth forecast error

didierodayo — Wed, 29 Mar 2017 02:09:10 GMT

Hi Sunny the declines in this scenario are actually errors that frequently show up in the data due to technical reasons. Will it be possible to add something like

(IF the current value is lower than the last 2 values then replace current value with the last value) to the sale data?

Re: Negative growth forecast error

sunny_talwar — Wed, 29 Mar 2017 08:20:53 GMT

So are you saying that before performing the regression analysis, check if Sale is greater than the last and last to last sale amount? If it is not, then replace it with last sale?

Re: Negative growth forecast error

didierodayo — Wed, 29 Mar 2017 08:27:57 GMT

Yes that is correct Sunny. If the Sale is greater or equal to each of the last 2 Sale values . Otherwise the value gets replaced by the last value.

Thanks

Re: Negative growth forecast error

sunny_talwar — Wed, 29 Mar 2017 08:34:53 GMT

May be this

Sales:

LOAD Date,

Product,

[Sale limit],

Sale

FROM

forecasting.xls

(biff, embedded labels, table is Sheet1$);

TempSales:

LOAD *,

If(Product = Previous(Product) and Sale < Previous(Sale) and Sale < Previous(Previous(Sale)), Peek('NewSales'), Sale) as NewSales

Resident Sales

Order By Product, Date;

DROP Table Sales;

SalesLimitHit:

LOAD Product,

Date((Only([Sale limit])-LINEST_B(NewSales, Date))/LINEST_M(NewSales, Date),'DD/MM/YYYY') as [Limit Hit Date]

Resident TempSales

Group By Product;

Re: Negative growth forecast error

didierodayo — Wed, 29 Mar 2017 08:47:18 GMT

Awesome. That is what I needed. Thanks a lot Sunnny.

Re: Negative growth forecast error

sunny_talwar — Wed, 29 Mar 2017 09:22:22 GMT

Great