Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Negative growth forecast error

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

Tags (2)
1 Solution

Accepted Solutions

Re: Negative growth forecast error

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;

7 Replies

Re: Negative growth forecast error

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?

Partner
Partner

Re: Negative growth forecast error

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

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?

Partner
Partner

Re: Negative growth forecast error

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

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;

Partner
Partner

Re: Negative growth forecast error

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

Re: Negative growth forecast error

Great