03-29-2022 06:13 PM - last edited on 05-19-2023 11:07 AM by JackLacava
Hi all,
We have a simple copy calculation on a forecast scenario that copies in the actuals data for certain periods, defined in a text field. We've had it for years and it generally works fine with no issues. This quarter though, I'm having an odd issue. There are a handful of intersections that are copying only USD values, but not local values and I cannot for the life of me figure out why. ANY suggestions or thoughts on what to review would be helpful!
Here's the calc on the scenario for reference:
Text2 field is 3 (technically "Firstforecastmonth=3") on the scenario and I'm looking at M1. There are about five accounts (out of hundreds) that are off, but inconsistently so there's not one specific piece of master data that seems to be driving the issue. Exchange rates are loaded and the entity with the issues is a CAD local entity that's translated into USD. The part that's truly baffling is that it's only copying in the USD value - here's an example with some blurry numbers:
The exact same formula calc above is working on another scenario and the scenario that's currently having issues has worked in years past. Let me know if there are any questions and I welcome any suggestions on what I can look into or test further.
Thanks!
-Nicole
Solved! Go to Solution.
03-31-2022 01:24 PM
HI Nicole - where is this calculation written? If it is NOT a Custom Calc, you could move this to FP 16. This would ensure its the last thing to run and if its successful, it likely points to a formula pass issue and/or another formula overwriting those numbers.
A few other unrelated items:
- this does not have any Entity conditions - does it need to run at parent Entities for any reason?
- the current month can easily be grabbed with api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId) rather than parsing the time name
03-30-2022 01:47 PM - edited 03-30-2022 01:47 PM
Is there also a translation formula on the Flow members that these items are hitting? If so, that may be a place to look as well.
03-30-2022 04:15 PM
@BobMarchese Thanks - the balances are on a base level flow member that doesn't have any formulas.
03-30-2022 05:18 PM
Nicole,
I had a similar issue with a calculation that never had any issues in the past but for some reason started giving me problems. After hours of banging my head against the wall and rerunning the rule over and over, I decided to restart IIS on all servers and retry. After doing this, the calculation worked as expected. Since restarting IIS is a simple process, maybe you can try that if you have done everything else you can think of.
03-31-2022 10:40 AM
Hi Mark - thanks so much for the suggestion! Reset servers in PROD and DEV and reran the consolidation/calc with no changes. Still having this odd issue but thanks for the suggestion as I wouldn't have thought of that.
03-31-2022 01:24 PM
HI Nicole - where is this calculation written? If it is NOT a Custom Calc, you could move this to FP 16. This would ensure its the last thing to run and if its successful, it likely points to a formula pass issue and/or another formula overwriting those numbers.
A few other unrelated items:
- this does not have any Entity conditions - does it need to run at parent Entities for any reason?
- the current month can easily be grabbed with api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId) rather than parsing the time name
03-31-2022 02:11 PM
Hi Eric,
The formula is on the specific scenario where the data is copied into so no ability to change formula passes (unless I'm missing something).
Re: Entity conditions, no, there's no real reason it needs to run at parent entities that I can think of. It's just the way it was built when we worked with OS support a couple years ago to update.
Re: month, the original formula was built in 2017 by Finit and that's the syntax they used. Thanks for the suggestion but if that part isn't broken, I'm likely not going to get approvals to change it.
03-31-2022 02:17 PM
An update to my original based on more testing, if I comment out the "api.Data.Calculate("S#..." line and add in an entity filter and a very specific calculate line, the data that's missing gets copied and then translates on it's own when I consolidate. I can't find any other calculations for this scenario, account, UD2 or UD4 that might be overwriting/wiping out the intersection when I have the full copy line in. Also, if I have the two calc lines (the original plus the one below), the specific intersection I'm having issues with doesn't copy. It only works if the more general calculate line is commented out.
Here's the filter and updated calculate line I tested that finally made that specific intersection appear:
FILTER: If (api.Cons.IsLocalCurrencyForEntity() ) Then
SPECIFIC CALC: api.Data.Calculate("S#LBE03_working:V#Periodic:A#ACC_SAP_535110:U2#PC_2287:U4#None = S#Actual:V#Periodic:A#ACC_SAP_535110:U2#PC_2287:U4#None")
03-31-2022 02:43 PM
Even though its a scenario formula, you could comment it out and then put the formula on another member (could create a new one for testing even), set it to FP16 and then paste the formula in there. But that is a very common troubleshooting technique which will give you more information to help diagnose.
If there are no reasons to run on parents, you could use If (Not api.Entity.HasChildren()) as a condition before the rule. It'll definitely speed things up by not running at Entities unnecessarily.
04-01-2022 09:50 AM
Hi Eric,
Thanks for the suggestion! I commented out the scenario formula entirely and added a new account member with the formula. It worked and copied everything correctly, no matter if the formula was set at FP1 or FP16. I'm honestly not sure what that's telling me though... The copy formula works when it's set on a revenue account (scenario type forecast and default time), no matter the formula pass number but it doesn't entirely work correctly when it's set on the forecast scenario itself.
I would appreciate any thoughts you might have! And thanks for getting me to this point.
04-01-2022 10:00 AM
Do you have any BRs assigned to BR 1 and 2 on the Cube? Those run between the Scenario formulas and FPs as part of the DUCS.
04-01-2022 10:57 AM
Hi Eric,
No, all business rules are (Not Used).