Forum Discussion

aricgresko's avatar
aricgresko
Contributor III
10 months ago

How to Get Prior Workflow Period Amount in Complex Expression

Is there a way to get the prior workflow period's amount ('Am' field in sourcestageData table)?

My use case is subtracting the current period's amount from the prior period's amount to derive the periodic value so I can apply Fx translation to just the periodic value instead of YTD value within a Derivative Transformation Rule.

I can easily get the current period's amount with this syntax: 

Dim amountValueCurr As Decimal = args.GetNumericValueUsingColName("Am")

I can also get the current and prior Workflow Time Key's, but unsure how to leverage the prior Wtk to get those amounts from the prior period.

Methodology
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    10 months ago

    There aren't many apis to look at stage data; there is FdxExecuteStageTargetTimePivot but it feels a bit overkill here. Most people will just go and query tables: open connection, sql etc. Doing it in transformation rules will likely be pretty slow though, even with caching hacks through globals. Besides, looking at stage data might mean you miss out on potentially important information loaded with forms, journals, or other workflows.

    I'll be that guy: I don't think this sort of operation belongs to Derivative Rules at all. You're doing financial calculations that are better (and more easily) done in the cube. I would just load stuff in the original source currency, to dummy entities or even a separate dummy cube if necessary, then do all the financial manipulation later in regular rules.

    • aricgresko's avatar
      aricgresko
      Contributor III
      10 months ago

      Hey Jack, thank you for the honest feedback and I've now come to that realization myself. 

      My original intention was 100% cost allocation from one Entity/UD2 intersection to another Entity/UD2 intersection within certain UD3 hierarchies.  This idea works exactly as intended, until you introduce Fx rates.  Then this idea fails in the second month because as I pull Fx rates into a complex expression on the Derivative Rule, the system will translate the YTD values instead of just the Periodic, hence why I was trying to fetch the prior periods amount and derive the current month's activity to isolate the translation.  

      Anyways, I'll go down the route of allocations within the Cube.

      Thanks again!