on 01-14-2014 7:48 AM
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
ACCOUNTS | TIME | INPUTCURRENCY | COSTCENTER | PRODUCT | SALESGROUP | DISTRIBCHANNEL | VERSION | COMPANY | METRIC | AUDITTRAIL | SIGNEDDATA |
NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | ZD2 | 10 | ACTUAL | DR01 | SAL_REV | BWUPLOAD | 238 |
COGS MODEL:
PRODUCT_RM | PRODUCT_SKU | ACCOUNTS | TIME | INPUTCURR | COSTCENTER | PRODUCT | DISTRCHANNEL | METRICS | UOM | VERSION | COMPANY | SIGNEDDATA |
---|---|---|---|---|---|---|---|---|---|---|---|---|
RM_CONCENTRATE | 000000000000000175 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000200000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 72 |
RM_CONCENTRATE | 000000000000000163 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000300000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 133 |
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_RM | PRODUCT_SKU | ACCOUNTS | TIME | INPUTCURR | COSTCENTER | PRODUCT | DISTRCHANNEL | METRICS | UOM | VERSION | COMPANY | SIGNEDDATA |
---|---|---|---|---|---|---|---|---|---|---|---|---|
RM_CONCENTRATE | 175 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000200000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 72 |
RM_CONCENTRATE | 163 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000300000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 133 |
RM_CONCENTRATE | 175 | NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | 10 | COGS_AMT | UN | BUDV0_2014 | DR01 | 25% * 238*(72/(72+133))=238*0.35=83.5 |
RM_CONCENTRATE | 163 | NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | 10 | COGS_AMT | UN | BUDV0_2014 | DR01 | 25% * 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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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
ACCOUNTS | TIME | INPUTCURRENCY | COSTCENTER | PRODUCT | SALESGROUP | DISTRIBCHANNEL | VERSION | COMPANY | METRIC | AUDITTRAIL | SIGNEDDATA |
NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | ZD2 | 10 | ACTUAL | DR01 | SAL_REV | BWUPLOAD | 238 |
COGS MODEL:
PRODUCT_RM | PRODUCT_SKU | ACCOUNTS | TIME | INPUTCURR | COSTCENTER | PRODUCT | DISTRCHANNEL | METRICS | UOM | VERSION | COMPANY | SIGNEDDATA |
---|---|---|---|---|---|---|---|---|---|---|---|---|
4000001 | 000000000000000175 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000200000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 72 |
4000002 | 000000000000000163 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000300000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 133 |
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_RM | PRODUCT_SKU | ACCOUNTS | TIME | INPUTCURR | COSTCENTER | PRODUCT | DISTRCHANNEL | METRICS | UOM | VERSION | COMPANY | SIGNEDDATA |
---|---|---|---|---|---|---|---|---|---|---|---|---|
4000001 | 175 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000200000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 72 |
4000002 | 163 | NO_ACC | 2014.01 | NO_INPUTCURR | NO_CC | 000030000300000001 | 10 | COGS_VOL | UN | BUDV0_2014 | DR01 | 133 |
4000001 | 175 | NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | 10 | COGS_AMT | UN | BUDV0_2014 | DR01 | 25% * 238*(72/(72+133))=238*0.35=83.5 |
4000001 | 163 | NO_ACC | 2014.01 | AED | NO_CC | 000030000200000001 | 10 | COGS_AMT | UN | BUDV0_2014 | DR01 | 25% * 238*(133/(72+133))=238*0.64=154.5 |
Regards,
Draksharam
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
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
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.