10 Replies Latest reply on Feb 28, 2016 11:24 AM by nickausaus

    Best method to store calculated results

    nickausaus

      Hi all

      I'm facing a challenge with a production forecasting tool I'm developing in FMP. My issue is that some of the calculations are based on other calculated fields, which results in the '?' being shown in the field I'm trying to calculate. An example:

       

      Stock at Dealership (end of this month) = Stock at Dealership (end of previous month) - Sales + Stock Purchased

      [cal_Close Dealer = Prev Month Data::cal_Close Dealer - Sales + Stock Purchased ]

       

      I'm using Table Occurrences to get the data from the previous month using self joins from Month to Prev Month

       

      I believe the issue is that I'm basically using the same calculated field (from previous month) in the calculation.

       

      I've tried creating a field of type number using 'Calculated Value' based on the Stock at Dealership field but this field remains blank.

       

      My current thinking is to use a script to set a field to be the value of the calculated field (e.g. actual_Close Dealer) and use this field in the calculation instead.

       

      Any advice on the best way to achieve this?

        • 1. Re: Best method to store calculated results
          bigtom

          I am wondering why there is a table for a previous month. Why not just have one table with all the monthly sales data.

           

          The beginning needs to be accounted for. This is usually the issue with this type of thing. Your first month has no data from the previous month and this means there can be no calculation for any month. This may be something you need to set manually in your calculation.

           

          For example...(basic idea with bad syntax)

           

          If (PrevMonth = known first month)

          Then cal_Close Dealer = "set value for first month"

          Else cal_Close Dealer = Prev Month Data::cal_Close Dealer - Sales + Stock Purchased

           

          Scripting can work as well, but it is hard to say with not knowing the exact way everything is setup.

          • 2. Re: Best method to store calculated results
            Mike_Mitchell

            Instead of using calculated fields, a system like this often benefits from using a transactional model. Whenever a sale is made, or stock comes in, you enter a record in a Transactions table. As part of that scripting, you update the totals for the month in plain vanilla number fields. Not only does this allow you to account for edge cases like "but there were no data from last month!" rather easily (since it's in a script), it will perform vastly better in the longer term.

            • 3. Re: Best method to store calculated results
              siplus

              When a numeric field on a layout shows "?" it simply means the field is not large enough to display the number. You might try making it larger.

              • 4. Re: Best method to store calculated results
                bigtom

                @siplus I doubt any car dealer stocks more than 99 vehicles on one lot. Could happen but not common. I'm sure the field is large enough. This does happen when there is a problem with the calc.

                • 5. Re: Best method to store calculated results
                  siplus

                  it takes 5 seconds to check that and rule it out , why not do it ?

                   

                  Numeric calcs rarely show ? as an answer (unless it's divide by 0 or the result of a malformed SQL) afaik.

                  • 6. Re: Best method to store calculated results
                    nickausaus

                    Hi All thanks for the replies. Its definitely not due to field size, I believe its more because the calculation is trying to reference itself (in the record for the previous month).

                     

                    bigtom I am actually using the same table. I have a history of data going back about 8 years, the calculation works fine if I use a historical number in the calculation (h_Close Dealer), but that value will not be present going forward.

                     

                    I'll try out something like what you've said using an 'IF', otherwise @Mike_Mitchell 's approach is the direction I'm heading in.

                    Thanks again all for your replies!

                    • 7. Re: Best method to store calculated results
                      bigtom

                      Mike's suggestion to go transactional is a better one.

                      • 8. Re: Best method to store calculated results
                        nickausaus

                        Thanks I think after some more reflection you're correct. I'm finding a few challenges as I go into backtesting which point in this direction.

                         

                        thanks again

                        • 9. Re: Best method to store calculated results
                          user19752

                          I don't think this is good way in this case, but you can change unstored field to stored using GetField() of Evaluate() on related field, like

                          Evaluate(”Prev Month Data::cal_Close Dealer”) - Sales + Stock Purchased

                           

                          This calculation should be calculated in order of older to newer, for correct result you need re-calculate it after sort. This makes it nonsense to be stored calculation.

                          Only applicable if first define the field in empty table, then insert records as ordered. But if so, auto-enter calculation with unstored calculation can be get same result.

                          • 10. Re: Best method to store calculated results
                            nickausaus

                            HI user19752

                            Thanks for the info. I will go back and see whether I can implement your suggestions. I'm facing a bit of a tight timeline this week but may be able to get back to it in a few days.

                             

                            What I am doing for now is the following:

                            1. data migration from old system (which has actual numeric data)

                            2. Running an initialisation script (one off as part of DM) which sets AC_* values, which represent actual values at that point in time

                            3. When user changes a value  (sales, stock received etc) I call a script trigger which updates the AC_ values based on the CAL_* fields as required.

                            I recognise it may not be the most efficient way.  But its working for now

                             

                            Thanks again.

                            nick