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?