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

Forecast Based on trend

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

1 Solution

Accepted Solutions
sunny_talwar

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')

Capture.PNG

View solution in original post

12 Replies
sunny_talwar

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

didierodayo
Partner - Creator III
Partner - Creator III
Author

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

sunny_talwar

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

didierodayo
Partner - Creator III
Partner - Creator III
Author

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

didierodayo
Partner - Creator III
Partner - Creator III
Author

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?

sunny_talwar

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')


Capture.PNG

didierodayo
Partner - Creator III
Partner - Creator III
Author

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

sunny_talwar

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')

Capture.PNG

didierodayo
Partner - Creator III
Partner - Creator III
Author

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

Best Regards