Negative growth forecast error

[didierodayo]

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];

[sunny_talwar]

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?

[didierodayo]

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?

[sunny_talwar]

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?

[didierodayo]

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

[sunny_talwar · Accepted solution]

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;

[didierodayo]

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

[sunny_talwar]

Great