topic Re: Forecast Based on trend in QlikView

Forecast Based on trend

didierodayo — Mon, 06 Feb 2017 16:52:59 GMT

Hello,

I need some help with this dummy scenario. Based on the usage below and the totals I am trying to forecast the date by which the total will be hit.

any help is appreciated.

Thanks

I have attached the qvw file

Re: Forecast Based on trend

sunny_talwar — Mon, 06 Feb 2017 17:25:15 GMT

Can you share what the expected output is and how you reach the expected output?

Re: Forecast Based on trend

didierodayo — Mon, 06 Feb 2017 17:34:23 GMT

Hi Synny,

am try to achieve something like.

where the runt out date is forecsted based on the in use growth overtime and the total for that station.

Thanks

Re: Forecast Based on trend

sunny_talwar — Mon, 06 Feb 2017 17:47:24 GMT

Not sure I understand the logic behind 15/07/2019? Can you share an Excel file with the logic?

Re: Forecast Based on trend

didierodayo — Mon, 06 Feb 2017 17:52:48 GMT

Sorry Sunny, I didn't write any logic to return that date I just showing the sample output.

But what I am trying to achieve is based on a linear growth return the date the In use will Hit 10 which is the current Total.

The current total can be different for other stations Thought. But I am happy to consider the max(Total) if that will work.

Thanks

Re: Forecast Based on trend

didierodayo — Tue, 07 Feb 2017 01:34:38 GMT

Hi Sunny,

I have managed to put something in qlik to get the date the total limit will be hit.

my problem is that as shown on the table in the document the Date in far in the past.

is something wrong with my expression?

Re: Forecast Based on trend

sunny_talwar — Tue, 07 Feb 2017 02:30:46 GMT

May be this:

Date(-LINEST_B({$<Date=>}TOTAL If(TrendDate=Date, Aggr(Sum({$<Date=>}[In Use]), Date), Null()), TrendDate)/LINEST_M({$<Date=>}TOTAL If(TrendDate=Date, Aggr(Sum({$<Date=>}[In Use]), Date), Null()), TrendDate),'DD/MM/YYYY')

Re: Forecast Based on trend

didierodayo — Tue, 07 Feb 2017 02:49:02 GMT

I applied and it is setting the same date for all the stations (close to 500 in the original app) when I don't have any station selected. but when I select a station then it returns the right date for that station? I have added 1 more station to the mix as shown below .

the app is also attached.

Thank you

Re: Forecast Based on trend

sunny_talwar — Tue, 07 Feb 2017 02:51:13 GMT

This may be?

Date(-LINEST_B({$<Date=>}TOTAL <Station> If(TrendDate=Date, Aggr(Sum({$<Date=>}[In Use]), Date,Station), Null()),

TrendDate)/LINEST_M({$<Date=>}TOTAL <Station> If(TrendDate=Date, Aggr(Sum({$<Date=>}[In Use]), Date,Station), Null()), TrendDate),'DD/MM/YYYY')

Re: Forecast Based on trend

didierodayo — Tue, 07 Feb 2017 02:58:41 GMT

Thanks Sunny. it worked like magic now I need to spend time and understand you expression.

Best Regards

Re: Forecast Based on trend

sunny_talwar — Tue, 07 Feb 2017 03:02:18 GMT

Its not that difficult... what I used is this

y = mx + b.... since you wanted to estimate the x intercept, I put y = 0

0 = mx + b => mx = -b => x = -b/m => -LinestB/LinestM

Re: Forecast Based on trend

sunny_talwar — Tue, 07 Feb 2017 03:02:56 GMT

The hard part was already done by you when you created the forecast expression.

Re: Forecast Based on trend

didierodayo — Tue, 07 Feb 2017 03:04:35 GMT

Thank You Sunny