Skip to Content
0
Jul 02, 2020 at 06:31 AM

Sum up Accounts with DATA and RESULTLOOKUP

974 Views Last edit Jul 02, 2020 at 06:36 AM 3 rev

Hi,

in my Data Action I want to sum up around 20 different accounts, lets say X01, X02, ... X20 on level of company code and profit center.

The accounts are not belonging to specific hierarchy nodes. I don´t want to create a new dimension attribute just only for being used to set the filter for summing up.

The values of the accounts are on different granularity, e.g.

  • X01 - X05 are per company code and profit center
  • X06 - X10 are per company code, profit center and product
  • X11 - X15 are per company code profit center and sales office
  • X16 - X20 are per company code profit center, product and sales office

The sum should be stored in Account Z.

What is the most perfomant (and easiest) way to achive this?

The current solution I have is like this

VARIABLEMEMBER #COST_A OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_B OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_C OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_D OF [d/GLACCOUNT]

DATA([d/GLACCOUNT] = #COST_A,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
        RESULTLOOKUP([d/GLACCOUNT] = "X01",[d/PRODUCT]="#",[d/SALESOFFICE]="#")+
        RESULTLOOKUP([d/GLACCOUNT] = "X02",[d/PRODUCT]="#",[d/SALESOFFICE]="#")+
        ...
        RESULTLOOKUP([d/GLACCOUNT] = "X05",[d/PRODUCT]="#",[d/SALESOFFICE]="#")

DATA([d/GLACCOUNT] = #COST_B,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
        RESULTLOOKUP([d/GLACCOUNT] = "X06",[d/SALESOFFICE]="#")+
        ...
        RESULTLOOKUP([d/GLACCOUNT] = "X10",[d/SALESOFFICE]="#")

DATA([d/GLACCOUNT] = #COST_C,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
        RESULTLOOKUP([d/GLACCOUNT] = "X06",[d/PRODUCT]="#")+
        ...
        RESULTLOOKUP([d/GLACCOUNT] = "X10",[d/PRODUCT]="#")

DATA([d/GLACCOUNT] = #COST_D,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
        RESULTLOOKUP([d/GLACCOUNT] = "X06")+
        ...
        RESULTLOOKUP([d/GLACCOUNT] = "X10")

DATA([d/GLACCOUNT] = "Z",[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
        RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_A) +
        RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_B) +
        RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_C) +
        RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_D)

Is this really performant? Is there an easier way?

Thanks - Thomas

PS: I´m missing an option to specify more than one value in the dimensionfilter in the RESULTLOOKUP function.