Advanced formula using Resultlookup does not work. Data is deleted from the nodes which are read

[amritagoswami]

Hi All,

We have a Data action to calculate the Cashflow Lines which are all Static GLs by copying data from certain P&L accounts or Balance Sheet accounts.

This code works in the non-prod environment.

However, when we execute in Prod no data in written in static accounts (S_990040, S_990053 etc) instead the existing data in P&L/ BS Nodes xx_0108, xx_0134 is getting wiped out.

Please help with any pointers you may have.

(Sample code below, same is happening for all other accounts in the code)

CONFIG.TIME_HIERARCHY = FISCALYEAR

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

CONFIG.GENERATE_UNBOOKED_DATA = OFF

MEMBERSET [d/Date] = %TargetYearFrom% TO %TargetYearTo%

MEMBERSET [d/XX_P_COMPCODE] = (%Entity%)

MEMBERSET [d/XX_P_PROFITCENTER] = (BASEMEMBER([d/XX_P_PROFITCENTER].[h/Hierarchy], "0CORPORATE", "0PROGRAM", "0PS", "0SC", "0SPARES"))

MEMBERSET [d/XX_P_AUDITTRAIL] = (BASEMEMBER([d/XX_P_AUDITTRAIL].[h/AuditHier], "ALL"))

VARIABLEMEMBER #TAX OF [d/XX_P_GLACCOUNT]

VARIABLEMEMBER #EBT OF [d/XX_P_GLACCOUNT]

// Net Income (EBT - Tax- sign reversed so that Loss is sent as -VE into Cashflow

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0134") THEN

DATA([d/XX_P_GLACCOUNT] = #EBT, [d/XX_P_AUDITTRAIL] = "CALC",[d/XX_P_COMPCODE]=%Entity%) = RESULTLOOKUP([d/XX_P_COMPCODE]=%Entity%)

ENDIF

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0108") THEN

DATA([d/XX_P_GLACCOUNT] = #TAX, [d/XX_P_AUDITTRAIL] = "CALC", [d/XX_P_COMPCODE]=%Entity%) = RESULTLOOKUP([d/XX_P_COMPCODE]=%Entity%)

ENDIF

DATA([d/XX_P_GLACCOUNT] = "S_990040", [d/XX_P_AUDITTRAIL] = "CALC",[d/XX_P_COMPCODE]=%Entity%) = (RESULTLOOKUP([d/XX_P_GLACCOUNT] = #EBT) - RESULTLOOKUP([d/XX_P_GLACCOUNT] = #TAX)) *-1

//IFRS16

DATA([d/XX_P_GLACCOUNT] = "S_990053", [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP([d/XX_P_GLACCOUNT] = "4007",[d/XX_P_COMPCODE]=%Entity%)

Thanks,

Amrita

[N1kh1l]

amritagoswami

Just structured the whole code.

Assumption is that for %Entity% leaf level members were passed as parameter value.

You should put up validated code from editor along with the Data Action parameter values used during code run to better assess the issue.

CONFIG.TIME_HIERARCHY = FISCALYEAR

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

CONFIG.GENERATE_UNBOOKED_DATA = OFF

MEMBERSET [d/Date] = %TargetYearFrom% TO %TargetYearTo%

MEMBERSET [d/XX_P_COMPCODE] = %Entity% // Needs to be in BASEMEMBER if parent nodes are passed as parameter value

MEMBERSET [d/XX_P_PROFITCENTER] = BASEMEMBER([d/XX_P_PROFITCENTER].[h/Hierarchy], "0CORPORATE", "0PROGRAM", "0PS", "0SC", "0SPARES")

MEMBERSET [d/XX_P_AUDITTRAIL] = BASEMEMBER([d/XX_P_AUDITTRAIL].[h/AuditHier], "ALL")

VARIABLEMEMBER #EBT OF [d/XX_P_GLACCOUNT]
VARIABLEMEMBER #TAX OF [d/XX_P_GLACCOUNT]
VARIABLEMEMBER #ROU OF [d/XX_P_GLACCOUNT] // Not being used

// Net Income (EBT - Tax- sign reversed so that Loss is sent as -VE into Cashflow

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0134") THEN

DATA([d/XX_P_GLACCOUNT] = #EBT, [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()

ENDIF

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0108") THEN

DATA([d/XX_P_GLACCOUNT] = #TAX, [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()

ENDIF

DATA([d/XX_P_GLACCOUNT] = "S_990040", [d/XX_P_AUDITTRAIL] = "CALC") = (RESULTLOOKUP([d/XX_P_GLACCOUNT] = #EBT, [d/XX_P_AUDITTRAIL] = "CALC") - RESULTLOOKUP([d/XX_P_GLACCOUNT] = #TAX, [d/XX_P_AUDITTRAIL] = "CALC")) *-1

//IFRS16

DATA([d/XX_P_GLACCOUNT] = "S_990053", [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP([d/XX_P_GLACCOUNT] = "4007")

Please see if this works.

Nikhil

[amritagoswami]

Same results with this code as well. No data written to S_990040 and data from XX_0134, XX_0108 is wiped out.

[N1kh1l]

That's strange. Can you change below. Remove [h/parentId]. Is it working in any tenant ? if yes any difference in two tenants?

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT], "XX_0134") THEN

DATA([d/XX_P_GLACCOUNT] = #EBT, [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()

ENDIF

[amritagoswami]

The code gives the same output after removing [h/parentId].

This code is working in Non Prod. This could be a data or system config issue, not sure if anyone has encountered anything like thus. I have compared the Account settings etc between the 2 tenants and I cannot find any difference.

[N1kh1l]

amritagoswami

You mean it works after removing [h/parentId]? Is there any CONFIG.HIERARCHY in the code ? are your Non Prod and Prod tenant on same version?

Nikhil

[amritagoswami]

• No it does not work after removing [h/parentId].
• There is no Config.Hierarchy in the code
• Prod and Non Prod are on same version

Versions2022.8.21 (Client)

2022.8.17 (Server)

Thanks,

Amrita

[N1kh1l]

amritagoswami

This is indeed very strange. Not calculating the stat account is ok but deleting the values of "XX_0134" base members is what is baffling me more as those are not referred in our data statement . Just try one last thing. Replace the #EBT variable with an actual stat account member (create one ) and see if only EBT is calculated or not.

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0134") THEN

DATA([d/XX_P_GLACCOUNT] = "EBTACCOUNT", [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()

ENDIF

[N1kh1l]

amritagoswami

You declared the below GL Account Variable to aggregate Account balances.

VARIABLEMEMBER #TAX OF [d/XX_P_GLACCOUNT]

VARIABLEMEMBER #ROU OF [d/XX_P_GLACCOUNT]

But in your DATA statement you have used. #EBT

IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0134") THEN<br>DATA([d/XX_P_GLACCOUNT] = <strong>#EBT</strong>, [d/XX_P_AUDITTRAIL] = "CALC",[d/XX_P_COMPCODE]=%Entity%) = RESULTLOOKUP([d/XX_P_COMPCODE]=%Entity%)

ENDIF

Did I miss the #EBT Declaration?

Also I hope %Entity%, you are passing only single base level Entity value when running this DA.

Can you try this code snippet. You can keep all scoping members intact. As %Entity% is already passed in memberset we need not write it again in DATA or RESULTLOOKUP unless we are changing the context.

VARIABLEMEMBER #EBT OF [d/XX_P_GLACCOUNT]
IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0134") THEN
DATA([d/XX_P_GLACCOUNT] =#EBT,[d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()
ENDIF


IF [d/XX_P_GLACCOUNT] = BASEMEMBER([d/XX_P_GLACCOUNT].[h/parentId], "XX_0108") THEN
DATA([d/XX_P_GLACCOUNT] = #TAX, [d/XX_P_AUDITTRAIL] = "CALC") = RESULTLOOKUP()
ENDIF


DATA([d/XX_P_GLACCOUNT] = "S_990040", [d/XX_P_AUDITTRAIL] = "CALC") = (RESULTLOOKUP([d/XX_P_GLACCOUNT] = #EBT,[d/XX_P_AUDITTRAIL] = "CALC") - RESULTLOOKUP([d/XX_P_GLACCOUNT] = #TAX,[d/XX_P_AUDITTRAIL] = "CALC")) *-1

Nikhil

[amritagoswami]

That was a typo. I do have a declaration for variable EBT similar to #ROU. The wrong variable declaration got copied here

[N1kh1l]

amritagoswami

I see that when you did EBT-TAX you missed Audit trail in your resultlookup.

DATA([d/XX_P_GLACCOUNT] = "S_990040", [d/XX_P_AUDITTRAIL] = "CALC") = (RESULTLOOKUP([d/XX_P_GLACCOUNT] = #EBT,[d/XX_P_AUDITTRAIL] = "CALC") - RESULTLOOKUP([d/XX_P_GLACCOUNT] = #TAX'[d/XX_P_AUDITTRAIL] = "CALC")) *-1

[amritagoswami]

The %Entity% was added into the ersultlookup lines while troubleshooting. Even without that the code did not work.

[N1kh1l]

amritagoswami

Ok. You are passing a single leaf level Entity during DA run ? Also please add AUDITTRAIL ="CALC" in your resultlookup as thats where you created the virtual records for EBT and TAX

DATA([d/XX_P_GLACCOUNT] = "S_990040", [d/XX_P_AUDITTRAIL] = "CALC") = (RESULTLOOKUP([d/XX_P_GLACCOUNT] = #EBT,[d/XX_P_AUDITTRAIL] = "CALC") - RESULTLOOKUP([d/XX_P_GLACCOUNT] = #TAX,[d/XX_P_AUDITTRAIL] = "CALC")) *-1

Nikhil