Data action not working correctly

[Victorcardona]

Hello colleagues, good day. I need your assistance. Could you please help me understand why this data action is not correctly adding these two accounts? As you can see in the table, accounts 7345602001 and 7345603501 should total 147 million for 202310 in the actual version. However, when I attempt to sum up these values into the temporary account called DEPRECIACION in the version pyg, the result is only 68 million. I have attached a screenshot of the data action. Thank you very much in advance for your help.

#sac #sapanalyticscloud #dataaction

[N1kh1l · Accepted solution]

@Victorcardona 

Note: Please paste your code as text if asking Data Action related question

In the table try to bring other dimensions of the model too. May be some other dimension has other value for this records.

Just try the below. This should work fine provided all other dimensions for the records also have same master data.

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = ON

MEMBERSET [d/Date] = BASEMEMBER ([d/Date]. [h/YM], "2023")
MEMBERSET [d/SOCIEDAD_FI]=("1000")
MEMBERSET [d/CUENTA_CONTABLE] = ("7345603501", "7345602001")
DELETE([d/CUENTA_CONTABLE] = "DEPRECIACION")
DATA([d/CUENTA_CONTABLE] = "DEPRECIACION") = RESULTLOOKUP ([d/Version] "public. Actual")

Br.

Nikhil

[Victorcardona · Accepted solution]

Thank you Nikhil for your help, but the data action is still not working as is supposed to. It is good to point out that I need to run the data actions for all dates. here is the Data action I am usiong with the additional memberset for the dimension "clase documento". There are only 2 left dimensions I did not include in the membersets because I need to run the data action for all their members, the dimensions are ORDERS and PLACAS.

CONFIG.TIME_HIERARCHY = CALENDARYEAR

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

CONFIG.GENERATE_UNBOOKED_DATA = ON

MEMBERSET [d/SOCIEDAD_FI]=("1000")

MEMBERSET [d/CUENTA_CONTABLE] = ("7345603501", "7345602001")

MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YM], "(all)"))

MEMBERSET [d/CLASE_DOCUMENTO] = "AF"

DELETE([d/CUENTA_CONTABLE] = "DEPRECIACION")

DATA([d/CUENTA_CONTABLE] = "DEPRECIACION") = RESULTLOOKUP ([d/Version] = "public.Actual")

[N1kh1l · Accepted solution]

Try adding the memberset for Measure also MEMBERSET [d/Measure]="importe_sociedad". The code looks fine but Just check the records for one month 202310 and see other dimension values also in table. Also It can happen that some members are outside hierrachy so include the below line also and put all dimensions of the model.

CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY = [d/SOCIEDAD_FI],[d/CUENTA_CONTABLE],[d/ORDERS],[d/PLACAS]

[Victorcardona · Accepted solution]

Hi Nikhil, I discovered what it is happening. I am getting this results because the data action is only running on the dimension members of PLACA which are in the hierachy "TOTAL UNIDAD FLOTA". Could you help me to run the data action for all the PLACAS dimension's members even for those which are not part of the hierachy?

[N1kh1l · Accepted solution]

Just Add this statement as the last config statement. You can also add other dimensions where you have similar problem

CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY =[d/PLACAS]

CONFIG.TIME_HIERARCHY = CALENDARYEAR

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

CONFIG.GENERATE_UNBOOKED_DATA = ON

MEMBERSET [d/SOCIEDAD_FI]=("1000")

MEMBERSET [d/CUENTA_CONTABLE] = ("7345603501", "7345602001")

MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YM], "(all)"))

MEMBERSET [d/CLASE_DOCUMENTO] = "AF"

DELETE([d/CUENTA_CONTABLE] = "DEPRECIACION")

DATA([d/CUENTA_CONTABLE] = "DEPRECIACION") = RESULTLOOKUP ([d/Version] = "public.Actual")

[Victorcardona · Accepted solution]

Hi Nikhil, Thank you for the help, I added the CONFIG.HIERARCHY you recommend me, however it is still not adding the values as it should. I am sure the problem is due the hierarchy of the dimension PLACAS because I am visualizing the source accounts (7345603501 and 7345602001) with Flat presentation for PLACAS and the total is the correct value of 147 Million for the actual version and when I add the hierarchy for PLACAS to the table, it shows the 68 Million I am getting with the data action.  DEPRECIACION it is still showing a total of 68 Million after running the data action with the CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY. is there sometime else I can do to fix this?

[Victorcardona · Accepted solution]

Hello Nikhil, I found the solution. additional to add the CONFIG.HIERARCHY statement in the data action script, I also had to go inside the PLACAS dimension and define empty hierarchy values equal to " Group and show under "Not in hierarchy". after doing this adjustment the data action is working correctly, thank you very much for your help!