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: 
niteen_kumar
Contributor
Contributor

How can I forecast for next three months using the below data?

Ticket Closed Year MonthTicket Count
2016-jun.16977
2016-jul.16126
2016-aug.17413
2016-sep.16923
2016-okt.17425
2016-nov.18854
2016-dec.17007
2017-jan.17647
2017-feb.16861
2017-mrt.20982
2017-apr.16430
2017-mei18587
2017-jun.16291
2017-jul.15215
2017-aug.15911
2017-sep.14385
2017-okt.15532
2017-nov??
2017-dec??
2018-jan??

Can somebody pls help me?

I used the formula: rangeavg(above(COUNT([Incident ID]),0,3)) to calculate the three month running average but the calculation stops at the month of october.

I also tried : linest_m(total aggr(if(Count([Incident ID]),Count([Incident ID])),[INC Closed Time.autoCalendar.YearMonth]),[INC Closed Time.autoCalendar.YearMonth])*only({1}[INC Closed Time.autoCalendar.YearMonth])+linest_b(total aggr(if(Count([Incident ID]),Count([Incident ID])),[INC Closed Time.autoCalendar.YearMonth]),[INC Closed Time.autoCalendar.YearMonth]) to calculate the predicted value for Nov/Dec/January but was an obviate attempt.

Looking forward to hear from the community.

Message was edited by: Niteen Kumar Hi Sunny, i have added the qvf file also. Looking forward for interesting solution. Cheers Nitin

6 Replies
sunny_talwar

Would you be able to share the qvf where you are trying this? Will be easy to troubleshoot from there

sunny_talwar

Hi Niteen -

I see you are only loading data until 2017-sep. If you won't have oct, nov, and dec in you app, they won't get calculated. Now this may be how it is only in the sample app, whereas your original app might have it. But this would be a good starting point to check. Do you even have those three months in your application or not?

Capture.PNG

niteen_kumar
Contributor
Contributor
Author

Hi Sunny, I don't have the next three months (oct/nov/dec) in my app and that is precisely my question. How can I show next three month and the corresponding predicted count of tickets based on the last three month ticket data?  These data files are monthly files with count of tickets

Cheers, Niteen.

sunny_talwar

Just concatenate those three into your main field

Table:

LOAD

    "Incident ID",

    "INC Closed Time"

FROM [lib://Downloads/Ticket Forecasting Problem.xlsx]

(ooxml, embedded labels, table is Sheet1);


Concatenate (Table)

LOAD Date(Date#("INC Closed Time", 'DD/MM/YYYY')) as "INC Closed Time"

INLINE [

INC Closed Time

01/10/2017

01/11/2017

01/12/2017

];

niteen_kumar
Contributor
Contributor
Author

Thanks Sunny. With your help i managed to get the months as a dimension. How about calculating the foretasted value for these months? How should the formula look like. Thanks again.

Cheers, Niteen

sunny_talwar

I think your initial formula should work... if it isn't would you be able to share the app with those dates in the app?