2 Replies Latest reply on May 21, 2013 9:23 AM by alainsainson

    How to forecast future data based on historical data?


      Dear forum


      I have created a database of 'parts'. A separate table links to the parts table called 'usage'.

      (for example, part number 1234 is in the 'parts' table.

      It has 3 different usage points in the 'usage' table- 10 used on 01/01/2011, 18 used on 01/03/2012, 32 used on 01/01/2013)


      I want to use this data to forecast usage in the future.


      For example, how many will be used in 2014?



      I know there is multiple ways to forcast based on trends- for example in Microsoft Excel there is linear, logarithnic, polynomial, power, exponential trend lines.


      What forecasting is available in Filemaker Pro 11?

      Can anyone help me with advice, good links to help me develop this, and or examples on forecasting used in filemaker pro 11??


      This would be of great help to me.


      Thank you



        • 1. Re: How to forecast future data based on historical data?

          Predicting the future can be a very deep rabbit hole to go down. FileMaker forecasting tools are going to be a bit more do-it-yourself than with Excel, but not by much if you're forecasting with any sophistication. Skeleton Key did a blog post series on using FileMaker to build tools for basic predictions; there's some use of the ExecuteSQL function introduced in FileMaker 12, but the concepts can be applied using FileMaker 11 just as well.

          • 2. Re: How to forecast future data based on historical data?

            I have been doing forcasting for a while now with data in FM db's (sales, inventory and attendance numbers), and have for the most part found it easier to dump data off into Excel or R (well worth looking into http://www.r-project.org if you aren't already familiar!) and then reimport coefficents periodically. This made a more sense for me as I tend to work on fixed scope projects more than ongoing data streams, and also allowed me to easily look at several models at the same time.


            As Jbante said, FM doesn't give you much help in this realm so you will need to create all the formulas  if you are looking for  "longterm" built in forecasting. Depending on the amount of data and the complexity of your model, this could become very taxing on your overall database performance. It sounds as if you are still trying to determin how best to model your forecasts. This I would definietly do outside the scope of Filemaker. If you have historical data that will be great. Play with it in Excel or R and see what model gives you the best fit. It will then be easier to recreate the math in your model within Filemaker if that is what you decide to do.


            One last thought... don't overlook a dumb and simple "at the current rate" calculation. Despite all it's limitations, you can get a usefull number with very little overhead.