6 Replies Latest reply: Feb 12, 2018 11:51 AM by Sunny Talwar

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

Niteen Kumar Feb 9, 2018 6:42 AM

Ticket Closed Year Month	Ticket 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-mei	18587
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

Ticket Analysis.qvf 3.2 MB
Ticket Forecasting Problem.xlsx 14.4 K

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

Sunny Talwar Feb 8, 2018 12:19 PM (in response to Niteen Kumar )

Would you be able to share the qvf where you are trying this? Will be easy to troubleshoot from there
Like Show 0 Likes (0)

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

Sunny Talwar Feb 10, 2018 7:49 PM (in response to Niteen Kumar )

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?
Like Show 0 Likes (0)

Actions
- Re: How can I forecast for next three months using the below data?
  
  Niteen Kumar Feb 11, 2018 4:36 PM (in response to Sunny Talwar )
  
  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.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How can I forecast for next three months using the below data?
    
    Sunny Talwar Feb 12, 2018 11:25 AM (in response to Niteen Kumar )
    
    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
    ];
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How can I forecast for next three months using the below data?
      
      Niteen Kumar Feb 12, 2018 11:38 AM (in response to Sunny Talwar )
      
      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
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: How can I forecast for next three months using the below data?
        
        Sunny Talwar Feb 12, 2018 11:51 AM (in response to Niteen Kumar )
        
        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?
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post