Forum Discussion

NicoleBruno's avatar
NicoleBruno
Valued Contributor
3 years ago

Copy Actual to Forecast issue

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

Finance
  • EricOsmanski's avatar
    EricOsmanski
    3 years ago

    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

  • BobMarchese's avatar
    BobMarchese
    New Contributor III
    3 years ago

    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.

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      BobMarchese  Thanks - the balances are on a base level flow member that doesn't have any formulas. 

  • Mark_DiMatteo's avatar
    Mark_DiMatteo
    New Contributor II
    3 years ago

    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.

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      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. 

  • EricOsmanski's avatar
    EricOsmanski
    Valued Contributor
    3 years ago

    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

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      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. 

       

  • NicoleBruno's avatar
    NicoleBruno
    Valued Contributor
    3 years ago

    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")

    • EricOsmanski's avatar
      EricOsmanski
      Valued Contributor
      3 years ago

      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.

      • NicoleBruno's avatar
        NicoleBruno
        Valued Contributor
        3 years ago

        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.