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.
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.
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.
@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.
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.
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!
Mike's suggestion to go transactional is a better one.
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.
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.
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