3 Replies Latest reply on Aug 27, 2009 3:17 PM by bigtone

    Item Consumption Rate



      Item Consumption Rate


      Once again I return to the oracles of this Forum.


      I have a product table which maintains all product details etc and a stock table which records goods coming in and going out [obviously].


      I'm trying to set up a fields which shows monthly consumption of particular products.


      Ideally I could add a second field which could allow you to alter number of months you would like consumption data returned for.


      For example the default is the last 3 months but you could alter that to 6 or 1. The calculation would only consider the stock that has left in the specified number of months prior divided my the said number of months to provide a monthly rate of consumption.


      Would this have to be a scrip or can it be done via calculation?


      I have managed get a field, through a calculation, to return a monthly consumption figure for all stock that has left over a time period but not been able to increase the complexity as I would like. The calculation is basically 'Stock out divided by number of months'


      Thanks in advance for any assistance, hopefully I will be able to reciprocate some of this help as my skill level improves.

        • 1. Re: Item Consumption Rate




          A Summary field allows you to total, average, count fields (and other options).  You can create a report that sub-summarizes by a field.  Putting the summary field in the area that sub-summarizes by month will give you the month totals.  If the field is set up for six months, then it will sub-summarize by six months.


          You can use the summary field in a calculation by using the GetSummary function.  For example,


          GetSummary ( <summary field> ; <break field> ) / <number of months>


          ... will give you a total summarized by the break field (month interval) divided by the number of months to get a monthly consumption number.


          This should get you pointed in the right direction.


          If you need clarification for any of the above steps, please let me know.



          FileMaker, Inc. 

          • 2. Re: Item Consumption Rate
               I think it would be rather easy to setup a field to select the number of months you want the consumption calculated for, use this value in a calculation that counts back the selected number of months, and then use that calculation in a relationship to the Stock table, only relating records where the 'stock out' data entry date is greater than or equal to that date.  Sounds like you are close to this already, you just need the calculated field and another relationship based on it.
            • 3. Re: Item Consumption Rate
                 Thanks chaps, I've kind of come up with an amalgamated solution.