cancel
Showing results for 
Search instead for 
Did you mean: 

LOOKUP AND ALLOCATION

former_member1013626
Participant
0 Kudos

Hi,

    I am on BPC 10 NW. My scenario is as follows:

The requirement is to calculate cost of RM_CONCENTRATE(COGS MODEL) as 25% of SAL_REV(REVENUE MODEL).

I have two models, REVENUE and COGS.

REVENUE MODEL

ACCOUNTSTIMEINPUTCURRENCYCOSTCENTERPRODUCTSALESGROUPDISTRIBCHANNELVERSIONCOMPANYMETRICAUDITTRAILSIGNEDDATA
NO_ACC2014.01AEDNO_CC000030000200000001ZD210ACTUALDR01SAL_REVBWUPLOAD238

COGS MODEL:

PRODUCT_RMPRODUCT_SKUACCOUNTSTIMEINPUTCURRCOSTCENTERPRODUCTDISTRCHANNELMETRICSUOMVERSIONCOMPANYSIGNEDDATA
RM_CONCENTRATE000000000000000175NO_ACC2014.01NO_INPUTCURRNO_CC00003000020000000110COGS_VOLUNBUDV0_2014DR0172
RM_CONCENTRATE000000000000000163NO_ACC2014.01NO_INPUTCURRNO_CC00003000030000000110COGS_VOLUNBUDV0_2014DR01133

As can be seen, COGS has no Sales Group while Revenue model has Sales group as additional dimension.

So, I want to run allocation cross-application using *LOOKUP and distribute SAL_REV proportionately based on PRODUCT_SKU's COGS_VOL quantities and write to COGS_AMT.

AFTER: COGS MODEL

PRODUCT_RMPRODUCT_SKUACCOUNTSTIMEINPUTCURRCOSTCENTERPRODUCTDISTRCHANNELMETRICSUOMVERSIONCOMPANYSIGNEDDATA
RM_CONCENTRATE175NO_ACC2014.01NO_INPUTCURRNO_CC00003000020000000110COGS_VOLUNBUDV0_2014DR0172
RM_CONCENTRATE163NO_ACC2014.01NO_INPUTCURRNO_CC00003000030000000110COGS_VOLUNBUDV0_2014DR01133
RM_CONCENTRATE175NO_ACC2014.01AEDNO_CC00003000020000000110COGS_AMTUNBUDV0_2014DR0125% * 238*(72/(72+133))=238*0.35=83.5
RM_CONCENTRATE163NO_ACC2014.01AEDNO_CC00003000020000000110COGS_AMTUNBUDV0_2014DR0125% * 238*(133/(72+133))=238*0.64=154.5

So, I believe I need to Lookup from COGS Model into Revenue Model and then do allocation. Any ideas how this could be achieved?

Regards,
Draksharam

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Hi Draksharam,

You can't use LOOKUP with *RUNALLOCATION, *RUNALLOCATION can be done within one cube only.

2 Options:

1. First copy Allocation key from REVENUE Model to COGS Model (to some account). Then do RUNALLOCATION in COGS model.

2. Instead of RUNALLOCATION you can try to create WHEN/REC/ENDWHEN in COGS with LOOKUP to REVENUE. But the performance will be lower then for RUNALLOCATION.

B.R. Vadim

former_member1013626
Participant
0 Kudos

Vadim,

    Thanks for the reply. I foresee some issues with *LOOKUP followed *WHEN/*REC/*ENDWHEN.

1. I will have to aggregate all the value under sales_group for a particular distribution channel for *lookup since the COGS model does not have SALES_GROUP dimension.

2. In *REC, I will have to aggregate all the values COGS_VOL of PRODUCT_SKU per distribution channel and divide each SKU's COGS_VOL and then multiply with *LOOKUP value so that the value is distributed proportionately.

Insight on the above issues is highly welcome.

Regards,
draksharam

former_member1013626
Participant
0 Kudos

Hi Vadim,

    At UJKT, I have tried the following code to implement the non-ALLOCATION method you suggested and I need some help here(Red font).

//Initializing variables

*SELECT(%TIME_VAR%,"[ID]",TIME,"[YEAR]='2014' AND [LEVEL]='MONTH'")

//LOOKUP to Revenue Model

*LOOKUP Revenue_planning

*DIM LOOKREV:ACCOUNTS=NO_ACC

*FOR %LOOP_TIME%=%TIME_VAR%

*DIM LOOKREV:TIME=%LOOP_TIME%

*NEXT

*DIM LOOKREV:INPUTCURRENCY=AED

*DIM LOOKREV:COSTCENTER=NO_CC

*DIM LOOKREV:PRODUCT=<ALL>

*DIM LOOKREV:SALES_GROUP=<ALL>

*DIM LOOKREV:DISTRIB_CHANNEL=10

*DIM LOOKREV:VERSION=BUDV0_2014

*DIM LOOKREV:COMPANY=DR01,DV01

*DIM LOOKREV:METRICS=SAL_VOL

*DIM LOOKREV:AUDITTRAIL=BWUPLOAD

*ENDLOOKUP

*XDIM_FILTER PRODUCT_RM=[PRODUCT_RM].PROPERTIES("MAT_GRP")="CONCNTRTE"

*XDIM_MEMBERSET METRICS=COGS_VOL

*XDIM_MEMBERSET DISTRIB_CHANNEL=10

//For each of the time period in 2014

*WHEN TIME

*IS *

//(Divide the value of COGS_VAL for each SKU by sum the values of COGS_VOL for all SKUs)*LOOKREV

*REC(EXPRESSION=%VALUE%/LOOKUP(LOOKREV))

*ENDWHEN

Regards,
Draksharam

former_member1013626
Participant
0 Kudos

Vadim,

     Also, in the above code, when tested at UJKT, I get error:

QUERY LOOKUP DATA FROM APPLICATION: Revenue_planning

UJO_READ:Members invalid On Dimension(SALES_GROUP)

The Revenue_planning Model has SALES_GROUP but COGS does not have SALES_GROUP dimension. How can I skip it?

Regards,
Draksharam

former_member186338
Active Contributor
0 Kudos

First Incorrect LOOKUP syntax, please read help and help samples before posting.

Hint: for each *DIM LOOKUPLABEL:DIMNAME=MEMBER only single MEMBER is allowed. If you don't specify the DIMNAME in LOOKUP the current member in REC record will be used for this dimension. Member property is also allowed in the LOOKUP.

Second - it's better not to use *XDIM_FILTER, use *SELECT() and *XDIM_MEMBERSET

Third - the REC formula is strange.

Do you have parent for PRODUCT_SKU?

Vadim

former_member186338
Active Contributor
0 Kudos

Something like this:

*LOOKUP Revenue_planning

*DIM INPUTCURRENCY="AED"

*DIM VERSION="ACTUAL"

*DIM METRIC="SAL_REV"

*DIM LOOKREV:SALES_GROUP="PARENT_SALES_GROUP" //Parent of all Sales Groups???

*DIM AUDITTRAIL="BWUPLOAD"

*ENDLOOKUP

*SELECT(%RM%,"[ID]",PRODUCT_RM,"[MAT_GRP]='CONCNTRTE'")

*XDIM_MEMBERSET PRODUCT_RM=%RM%

*XDIM_MEMBERSET METRICS=COGS_VOL

*XDIM_MEMBERSET DISTRIB_CHANNEL=10 //Same in LOOKUP

*XDIM_MEMBERSET TIME=BAS(2014.TOTAL) //Same in LOOKUP

*XDIM_MEMBERSET ACCOUNT=NO_ACC //Same in LOOKUP

*XDIM_MEMBERSET INPUTCURRENCY=NO_INPUTCURR

*XDIM_MEMBERSET COSTCENTER=NO_CC //Same in LOOKUP

*XDIM_MEMBERSET VERSION=BUDV0_2014

*XDIM_MEMBERSET UOM=UN

*XDIM_MEMBERSET PRODUCT=<ALL> //Same in LOOKUP

*XDIM_MEMBERSET PRODUCT_SKU=<ALL>

*XDIM_MEMBERSET COMPANY=DR01,DV01 //Same in LOOKUP

*WHEN TIME

*IS *

// PARENT_PRODUCT_SKU - Parent of all PRODUCT_SKU

*REC(EXPRESSION=0.25*LOOKUP(LOOKREV)*%VALUE%/[PRODUCT_SKU].[PARENT_PRODUCT_SKU],METRICS="COGS_AMT",INPUTCURRENCY="AED")

*ENDWHEN

Vadim

former_member186338
Active Contributor
0 Kudos

And please, do not post wide multicolumn tables here - very hard to read. Show members in single column!

former_member1013626
Participant
0 Kudos

Hi Vadim,

     Thanks for the reply.

In addition to [PRODUCT_SKU].[PARENT_PRODUCT_SKU], the calculation also has to include all PRODUCT_RM belonging to MAT_GRP=CONCNTRTE.

So, like,(the syntax has gone wrong)

*REC(EXPRESSION=0.25*LOOKUP(LOOKREV)*%VALUE%/([PRODUCT_SKU].[PARENT_PRODUCT_SKU],[PRODUCT_RM].MAT_GRP="CONCNTRTE"), METRICS="COGS_AMT",INPUTCURRENCY="AED")

Because, the allocated value to each of PRODUCT_RM in COGS model should be equal to the total looked up value from Revenue model.

Could you please correct the syntax?


Regards,

Draksharam

former_member186338
Active Contributor
0 Kudos

Hi Draksharam,

For sure the syntax like provided is absolutely wrong, this is not a tuple expression:

([PRODUCT_SKU].[PARENT_PRODUCT_SKU],[PRODUCT_RM].MAT_GRP="CONCNTRTE")


And it's not possible to use property to aggregate! You have to use parents.


But I do not understand the logic "the calculation also has to include all PRODUCT_RM belonging to MAT_GRP=CONCNTRTE" In the original post the sample doesn't provide any info about it. And the code was written based on this sample.


Please show the READABLE sample with figures.


Vadim

former_member1013626
Participant
0 Kudos

Hi Vadim,

    Excuse me I am posting multi-column tables again since I do not know another way of explaining this.

SAL_REV from revenue model = 238.

Distributed over PRODUCT_RM=400001,400002 as 83.5 and 154.5 respectively so 83.5+154.5 = 238.0.

The calculation is:

25%*238(=LOOKUP(LOOKREV))* (72(=%VALUE%)/72+133(=[PRODUCT_SKU].[ALL_SKU_PARENT],[PRODUCT_RM].[CONCNTRATE_RM_PARENT])

or

25%*LOOKUP(LOOKREV) * %VALUE%/([PRODUCT_SKU].[ALL_SKU_PARENT],[PRODUCT_RM.[CONCNTRATE_RM_PARENT])

Sorry, before I gave RM as one member only but they are many members grouped under property MAT_GRP=CONCNTRTE. So, the logic.

Edited the sample from original post:

REVENUE MODEL

ACCOUNTSTIMEINPUTCURRENCYCOSTCENTERPRODUCTSALESGROUPDISTRIBCHANNELVERSIONCOMPANYMETRICAUDITTRAILSIGNEDDATA
NO_ACC2014.01AEDNO_CC000030000200000001ZD210ACTUALDR01SAL_REVBWUPLOAD238

COGS MODEL:

PRODUCT_RMPRODUCT_SKUACCOUNTSTIMEINPUTCURRCOSTCENTERPRODUCTDISTRCHANNELMETRICSUOMVERSIONCOMPANYSIGNEDDATA
4000001000000000000000175NO_ACC2014.01NO_INPUTCURRNO_CC00003000020000000110COGS_VOLUNBUDV0_2014DR0172
4000002000000000000000163NO_ACC2014.01NO_INPUTCURRNO_CC00003000030000000110COGS_VOLUNBUDV0_2014DR01133

As can be seen, COGS has no Sales Group while Revenue model has Sales group as additional dimension.

So, I want to run allocation cross-application using *LOOKUP and distribute SAL_REV proportionately based on PRODUCT_SKU's COGS_VOL quantities and write to COGS_AMT.

AFTER: COGS MODEL

PRODUCT_RMPRODUCT_SKUACCOUNTSTIMEINPUTCURRCOSTCENTERPRODUCTDISTRCHANNELMETRICSUOMVERSIONCOMPANYSIGNEDDATA
4000001175NO_ACC2014.01NO_INPUTCURRNO_CC00003000020000000110COGS_VOLUNBUDV0_2014DR0172
4000002163NO_ACC2014.01NO_INPUTCURRNO_CC00003000030000000110COGS_VOLUNBUDV0_2014DR01133
4000001175NO_ACC2014.01AEDNO_CC00003000020000000110COGS_AMTUNBUDV0_2014DR0125% * 238*(72/(72+133))=238*0.35=83.5
4000001163NO_ACC2014.01AEDNO_CC00003000020000000110COGS_AMTUNBUDV0_2014DR0125% * 238*(133/(72+133))=238*0.64=154.5

Regards,

Draksharam

former_member186338
Active Contributor
0 Kudos

Not able to read it!

Please, attach the Excel file with the detailed example. It's very simple!

Add number of PRODUCT, PRODUCT_SKU, PRODUCT_RM...

Show the property MAT_GRP

Looks like a mistake in the last line of PRODUCT_RM column of AFTER: COGS MODEL

And please, as I already told you, provide all info from the very beginning, I am wasting time when you suddenly remember something forgotten.

Vadim

former_member1013626
Participant
0 Kudos

Hi Vadim,

    I have got it. I created a parent 'CONCNTRTE' for RM members filtered by property 'MAT_GRP=CONCNTRTE'. and it worked properly. Using this parent, I could add up per PRODUCT, all SKUs and RM with property MAT_GRP=CONCNTRTE using tuple [PRODUCT_SKU].[ALL],[PRODUCT_RM].[CONCNTRETE].

Here's the code.

/Initializing variables

*SELECT(%TIME_VAR%,"[ID]",TIME,"[YEAR]='2014' AND [LEVEL]='MONTH'")

*SELECT(%PRD_VAR%,"[ID]",PRODUCT,"[CALC]='N'")

*SELECT(%RM_VAR%,"[ID]",PRODUCT_RM,"[MAT_GRP]='CONCNTRTE'")

//LOOKUP to Revenue

*LOOKUP Revenue_planning

*DIM LOOKREV:ACCOUNTS=NO_ACC

*DIM LOOKREV:INPUTCURRENCY=AED

*DIM LOOKREV:COSTCENTER=NO_CC

*DIM LOOKREV:SALES_GROUP=ALL

*DIM LOOKREV:DISTRIB_CHANNEL=10

*DIM LOOKREV:VERSION=BUDV0_2014

*DIM LOOKREV:COMPANY=DR01

*DIM LOOKREV:METRICS=AMOUNT

*DIM LOOKREV:AUDITTRAIL=INPUT

*ENDLOOKUP

*XDIM_MEMBERSET PRODUCT_RM=%RM_VAR%

*XDIM_MEMBERSET METRICS=COGS_VOL

*XDIM_MEMBERSET DISTRIB_CHANNEL=10

*XDIM_MEMBERSET TIME=%TIME_VAR%

*XDIM_MEMBERSET ACCOUNTS=NO_ACC

*XDIM_MEMBERSET COMPANY=DR01

*XDIM_MEMBERSET COSTCENTER=NO_CC

*XDIM_MEMBERSET INPUTCURRENCY=NO_INPUTCURR

*XDIM_MEMBERSET PRODUCT=<ALL>

*XDIM_MEMBERSET PRODUCT_SKU=<ALL>

*XDIM_MEMBERSET UOM=UN

*XDIM_MEMBERSET VERSION=BUDV0_2014

*WHEN PRODUCT

*IS *

*REC(EXPRESSION=%VALUE%*(LOOKUP(LOOKREV)/([PRODUCT_SKU].[ALL],[PRODUCT_RM].[CONCNTRTE])),METRICS="COGS_AMT",INPUTCURRENCY="AED")

*ENDWHEN

As always, thanks very much for the help.

Regards,
Draksharam

Answers (0)