Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
didierodayo
Partner - Creator III
Partner - Creator III

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

7 Replies
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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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

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
Partner - Creator III
Partner - Creator III
Author

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

sunny_talwar

Great