on 12-05-2017 3:02 PM
Hello BPC community and experts,
I'm currently struggling with the following problematic :
Let's suppose that I have the following dimensions :
CATEGORY : BUDGET
TIME : 2017.12
INDICATOR : VOLUME, EXPENSE
PRODUCT : A, B, C, D, E, F, G, H, etc.
The PRODUCT dimensions is built as follow :
I need to allocate the product that carries the expense on the other products of the same node, using the volume indicators.
For exemple : D is carrying the expense and should be allocated on the node ABCD
So my logic script would be :
This logic script works fine.
However I would like to dynamise it and to repeat the allocation to every nodes.
The next logical allocation would be the EXPENSE carried by H on the EFGH node...and so on. There is always a member of every node that carries the expense and should be allocated on the other members of the same node.
As anyone got an idea ?
Your advices will be really helpful.
Regards,
Karim
Create a property in PRODUCT dimension like ALLOCPARENT
Fill it:
For D - ABCD
For H - EFGH
...
In the script:
*SELECT(%SRCS%,[ID],PRODUCT,ALLOCPARENT<>'')
*SELECT(%PARS%,[ALLOCPARENT],PRODUCT,ALLOCPARENT<>'')
*FOR %SRC%=%SRCS% AND %PAR%=%PARS%
...
*RUNALLOCATION
...
*DIM PRODUCT WHAT=%SRC%; WHERE=BAS(%PAR%);...
...
*NEXT
Easy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, it should be really easy but I'm still struggling as I already tried to work with *select and properties.
However, I tried your advices.
Here is y real case (I scoped on 2 products : 7001 & 7002) :
(In terms of data source, the indicators "CKG_HORS_BTL" and "VOLUME_EQB" are on the same audit trail : INIT01 // the destination is ALLOC_MKG_MP01)
In the case above, I need to allocate the "CKG_HORS_BTL" (= EXPENSE) of product 7001 on the node MP011 using the "VOLUME_EQB" (= VOLUME). I need to it also for the product 7002 on the node MP002.
Therefore, I used your method and created a property "ALLOC_PARENT" in the PRODUCT dimension :
Below is my logical script :
*XDIM_MEMBERSET SOURCE_AUDIT = INIT01
*XDIM_MEMBERSET PRODUIT = %SRC%
*XDIM_MEMBERSET FORMATS = <ALL>
*XDIM_MEMBERSET CONDITIONNEMENT = <ALL>
*XDIM_MEMBERSET CLIENT = BAS(GD)
*XDIM_MEMBERSET TYPE_ACTION = <ALL>
*XDIM_MEMBERSET DEVISE = <ALL>
*XDIM_MEMBERSET CENTRE_PROFIT = <ALL>
*XDIM_MEMBERSET NC_IND = CKG_HORS_BTL, VOLUME_EQB
*XDIM_MEMBERSET SOCIETE = <ALL>
*XDIM_MEMBERSET TEMPS = %MOIS_12%
*XDIM_MEMBERSET PHASE = %PHASE_SET%
*FOR %SRC%=%SRCS% AND %PAR%=%PARS%
*RUNALLOCATION
*FACTOR = USING / TOTAL
*DIM PRODUIT WHAT = %SRC% ;WHERE = BAS(%PAR%) ; USING = <<<; TOTAL = <<<
*DIM CLIENT WHAT = MT_GD ;WHERE = BAS(GD) ; USING = <<<; TOTAL = <<<
*DIM TYPE_ACTION WHAT = NA_TYPE_ACTION ;WHERE <>NA_TYPE_ACTION ; USING = <<<; TOTAL = <<<
*DIM NC_IND WHAT = CKG_HORS_BTL; WHERE = <<< ; USING = VOLUME_EQB ; TOTAL = <<<
*DIM SOURCE_AUDIT WHAT = INIT01; WHERE = ALLOC_CKG_MP01
*DIM CENTRE_PROFIT WHAT =C03; WHERE <> NA_CENTRE_PROFIT ; USING = <<<; TOTAL = <<<
*ENDALLOCATION
*NEXT
<br>
The logical only worked for the 7001 product.
Hello Vadim,
The waiting is due to the jet lag as I think that we are not in the same continent...
I apologize for this.
I forgot to put the *SELECT indeed when I copied the script on the comment.
However it is present in my script.
Below the logical script completed with the *select.
*SELECT(%SRCS%,[ID],PRODUIT,"[ALLOC_PARENT<>''")
*SELECT(%PARS%,[ALLOC_PARENT],PRODUIT,"[ALLOC_PARENT]<>''")
*XDIM_MEMBERSET SOURCE_AUDIT = INIT01
*XDIM_MEMBERSET PRODUIT = %SRC%
*XDIM_MEMBERSET FORMATS = <ALL>
*XDIM_MEMBERSET CONDITIONNEMENT = <ALL>
*XDIM_MEMBERSET CLIENT = BAS(GD)
*XDIM_MEMBERSET TYPE_ACTION = <ALL>
*XDIM_MEMBERSET DEVISE = <ALL>
*XDIM_MEMBERSET CENTRE_PROFIT = <ALL>
*XDIM_MEMBERSET NC_IND = CKG_HORS_BTL, VOLUME_EQB
*XDIM_MEMBERSET SOCIETE = <ALL>
*XDIM_MEMBERSET TEMPS = %MOIS_12%
*XDIM_MEMBERSET PHASE = %PHASE_SET%
*FOR %SRC%=%SRCS% AND %PAR%=%PARS%
*RUNALLOCATION
*FACTOR = USING / TOTAL
*DIM PRODUIT WHAT = %SRC% ;WHERE = BAS(%PAR%) ; USING = <<<; TOTAL = <<<
*DIM CLIENT WHAT = MT_GD ;WHERE = BAS(GD) ; USING = <<<; TOTAL = <<<
*DIM TYPE_ACTION WHAT = NA_TYPE_ACTION ;WHERE <>NA_TYPE_ACTION ; USING = <<<; TOTAL = <<<
*DIM NC_IND WHAT = CKG_HORS_BTL; WHERE = <<< ; USING = VOLUME_EQB ; TOTAL = <<<
*DIM SOURCE_AUDIT WHAT = INIT01; WHERE = ALLOC_CKG_MP01
*DIM CENTRE_PROFIT WHAT =C03; WHERE <> NA_CENTRE_PROFIT ; USING = <<<; TOTAL = <<<
*ENDALLOCATION
*NEXT
I understand well the using of the *SELECT function and YES I understood your answer and already tried it before posting my question.
What I don't understand is why the calcul only work for one product and not for the other as I put as you recommended a *FOR/*NEXT function...
Regards,
Karim
The correct script will be:
*SELECT(%SRCS%,[ID],PRODUIT,"[ALLOC_PARENT<>''")
*SELECT(%PARS%,[ALLOC_PARENT],PRODUIT,"[ALLOC_PARENT]<>''")
*FOR %SRC%=%SRCS% AND %PAR%=%PARS%
// Scope INSIDE FOR/NEXT
*XDIM_MEMBERSET FORMATS = <ALL>
*XDIM_MEMBERSET CONDITIONNEMENT = <ALL>
*XDIM_MEMBERSET DEVISE = <ALL>
*XDIM_MEMBERSET SOCIETE = <ALL>
*XDIM_MEMBERSET TEMPS = %MOIS_12%
*XDIM_MEMBERSET PHASE = %PHASE_SET%
*RUNALLOCATION
*FACTOR = USING / TOTAL
*DIM PRODUIT WHAT = %SRC% ;WHERE = BAS(%PAR%) ; USING = <<<; TOTAL = <<<
*DIM CLIENT WHAT = MT_GD ;WHERE = BAS(GD) ; USING = <<<; TOTAL = <<<
*DIM TYPE_ACTION WHAT = NA_TYPE_ACTION ;WHERE <>NA_TYPE_ACTION ; USING = <<<; TOTAL = <<<
*DIM NC_IND WHAT = CKG_HORS_BTL; WHERE = <<< ; USING = VOLUME_EQB ; TOTAL = <<<
*DIM SOURCE_AUDIT WHAT = INIT01; WHERE = ALLOC_CKG_MP01
*DIM CENTRE_PROFIT WHAT =C03; WHERE <> NA_CENTRE_PROFIT ; USING = <<<; TOTAL = <<<
*ENDALLOCATION
*NEXT
*ENDALLOCATION statement will reset scope - you have to scope again inside FOR/NEXT loop.
You don't need to scope dimensions defined inside RUNALLOCATION.
Test the script in UJKT and you will see the results of FOR/NEXT.
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.