cancel
Showing results for 
Search instead for 
Did you mean: 

Script Logic on Agrregrate Values

Former Member
0 Kudos

Dear Experts,

We are having BPC 7.5 NW

We are having a requirement where, we need to write a  script logic which contained in different flows containing different material.

Example :

Opening value for inventory GL say  2001010 is 10000  with  NA as a material class and F_100 (opening flow )

Now user will plan for various materials classes for its consumption and minimum stock balance  under this same GL , example

                F_130    F_410

CS-EM     500          20

CS-ED     500          20

with F_130  (deduction flow)

       F_410   (minimum stock balance flow)

Now , I want to calculate closing for same GL with calculation as below :

First I will transfer the opening to a new flow with condition that only opening values which are above 0 should be transfer:

  F_INV  >=  F_100

For this we have developed a script logic as below :

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

//*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

//*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET FLOW = F_100

//Inverse Flow calculation

*FOR %M1% = %TIME_SET%

*WHEN FLOW

*IS F_100

*REC(EXPRESSION = %VALUE% > 0 ? %VALUE%:0,FLOW = F_INV )    

*ENDWHEN

*NEXT

*COMMIT

This transfer all the openings to F_INV flow with NA material class.

So in our case , since opening value is 10000, which is greater than  0 , F_INV will have 10000.

Now for closing the calculation is :

Closing = IF ( (Deduction + minimum stock balance - F_INV )  >   0 , Value , 0)

that means , logic should calculate as below

10000 - ((500+500)+(20+20)) = 8060

Now since this is above zero (0) , it should hit the  closing flow.

We have developed below script logic  :

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

//addition Flow calculation

*WHEN P_MATERIAL

*IS BAS(ALL_MAT)

*WHEN FLOW

*IS F_130

*REC(EXPRESSION = %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV]:0,FLOW = F_120)

*IS F_410

*REC(EXPRESSION = %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV]:0,FLOW = F_120)

*IS F_INV

*REC(EXPRESSION = [FLOW].[F_130]+[FLOW].[F_410]-%VALUE% > 0 ? [FLOW].[F_130]+[FLOW].[F_410]-%VALUE%:0,FLOW = F_120)

*ENDWHEN

*ENDWHEN

    

But the logic is calculating the closing  considering the F_INV flow as zero for all the material classes since it is zero.

How can we achieve this.

Thanks in advance.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Dear Vadim,

the script which you suggested for addition column value is not working.

My assumptions from your script.

From first section  (//Preliminary calculation - sum F_130,F_410 for all materials) First we add the deduction+min. stock balance for all material into addition.

Lets take example that deduction+min. stock balance for all material is 400000.

Then as per second section (//Preliminary calculation - subtract F_INV for NA)  we add the negative F_INV into the same addition value, i.e in our case the F_INV is 396000, so after after execution of second section, the value will be added to addition as  400000 + ( - 396000) which should be equal to 4000, but when executed, it is overwriting the value with -396000 resulting in wrong calculation.

Kindly assist.

former_member186338
Active Contributor
0 Kudos

Well, yes small mistake Easy to correct:

*XDIM_MEMBERSET P_MATERIAL=NA

*XDIM_MEMBERSET FLOW = F_INV

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=[FLOW].[F_120]-%VALUE%,FLOW=F_120) //subtract F_INV

*ENDWHEN

Vadim

former_member186338
Active Contributor
0 Kudos

P.S. If you have F_INV value only in P_MATERIAL=NA you can combine first and second loop:

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV

*WHEN FLOW

*IS F_INV

*REC(EXPRESSION=-%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store "-F_INV"

*ELSE

*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410

*ENDWHEN

Negative F_INV on NA will be accumulated with F_130,F_410 for All_MAT

Vadim

Former Member
0 Kudos

Dear Vadim,

Thank you Sir, but I am confused now , can you please combine the codes and provide.

Thanks once again!!

former_member186338
Active Contributor
0 Kudos

What's the issue?

First combined option is:

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

//Preliminary calculation - sum F_130,F_410 for all materials

*XDIM_MEMBERSET FLOW = F_130,F_410

*WHEN P_MATERIAL

*IS * // BAS(All_MAT) - already scoped

*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410

*ENDWHEN

//Preliminary calculation - subtract F_INV for NA

*XDIM_MEMBERSET P_MATERIAL=NA

*XDIM_MEMBERSET FLOW = F_INV

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=[FLOW].[F_120]-%VALUE%,FLOW=F_120) //subtract F_INV

*ENDWHEN

//Final

*XDIM_MEMBERSET FLOW = F_120

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120

*ENDWHEN

Second option requires understanding what data you have in F_INV!

Vadim

former_member186338
Active Contributor
0 Kudos

Additional question - is NA also in BAS(All_MAT)?

Former Member
0 Kudos

YES

former_member186338
Active Contributor
0 Kudos

If NA is not in BAS(All_MAT) then the second code option will be:

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

//Preliminary calculation - sum F_130,F_410 for all materials and subtract F_INV

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_ADDMEMBERSET P_MATERIAL=NA

*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV

*WHEN FLOW

*IS F_130,F_410

  *WHEN P_MATERIAL

  *IS BAS(All_MAT)

    *REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //sum of F_130,F_410

  *ENDWHEN

*IS F_INV

  *WHEN P_MATERIAL

  *IS NA

    *REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV

  *ENDWHEN

*ENDWHEN //Accumulation in this loop will provide correct results!

//Final - zero negative in F_120

*XDIM_MEMBERSET FLOW = F_120

*XDIM_MEMBERSET P_MATERIAL=NA

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120

*ENDWHEN

Vadim

former_member186338
Active Contributor
0 Kudos

Then, please describe the dimension P_ASSETMATERIAL:

Is All_MAT is a single root node?

And under this node you have NA and other members?

Message was edited by: Vadim Kalinin Corrected not ASSET but MATERIAL

former_member186338
Active Contributor
0 Kudos

IF NA is in BAS(All_MAT):

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

//Preliminary calculation - sum F_130,F_410 for all materials and subtract F_INV

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT) //NA is also in scope

*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV

*WHEN P_MATERIAL

*IS NA

*WHEN FLOW

*IS F_130,F_410

*REC(EXPRESSION=%VALUE%,FLOW=F_120) //sum of F_130,F_410 for NA

*ENDWHEN

*IS F_INV

*REC(EXPRESSION=-[FLOW].[F_120]-%VALUE%,FLOW=F_120) //subtract F_INV

*ENDWHEN

*ELSE // all other members of BAS(All_MAT) except NA

*WHEN FLOW

*IS F_130,F_410

*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //sum of F_130,F_410 for All

*ENDWHEN

*ENDWHEN

//Final

*XDIM_MEMBERSET FLOW = F_120

*XDIM_MEMBERSET P_MATERIAL=NA

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120

*ENDWHEN

Vadim

former_member186338
Active Contributor
0 Kudos

Can you clarify what do you mean by "But the logic is calculating the closing  considering the F_INV flow as zero for all the material classes since it is zero."??

Vadim

Former Member
0 Kudos

Dear Vadim,

There were mistake while writing the query ,

I have rectified it in below :

Our closing for all balance sheet items is :

Closing =  Opening + Addition - Deduction

Dear Experts,

We are having BPC 7.5 NW

We are having a requirement where, we need to write a  script logic which contained in different flows containing different material.

Example :

Opening value for inventory GL say  2001010 is 10000  with  NA as a material class and F_100 (opening flow )

Now user will plan for various materials classes for its consumption and minimum stock balance  under this same GL , example

             F_130    F_410

CS-EM    500          20

CS-ED    500          20

with F_130   (deduction flow)

F_410   (minimum stock balance flow)

Now , I want to calculate addition flow  for same GL with calculation as below :

First I will transfer the opening to a new flow with condition that only opening values which are above 0 should be transfer:

  F_INV  >= F_100

For this we have developed a script logic as below :

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

//*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

//*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET FLOW = F_100

//Inverse Flow calculation

*FOR %M1% = %TIME_SET%

*WHEN FLOW

*IS F_100

*REC(EXPRESSION = %VALUE% > 0 ? %VALUE%:0,FLOW = F_INV )   

*ENDWHEN

*NEXT

*COMMIT

This will transfer all the openings to F_INV flow with NA material class.

So in our case , since opening value is 10000, which is greater than  0 , F_INV will have 10000.

Now for addition  the calculation is :

Addition  = IF ( (Deduction + minimum stock balance - F_INV )  >   0 , Value , 0)

that means , logic should calculate as below

((500+500)+(20+20)) - 10000 = - 8060

Now since this is below zero (0) , zero (0)  should hit the  closing flow but it is hitting  (500+20) in each material class wise.

We have developed below script logic  :

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

//addition Flow calculation

*WHEN P_MATERIAL

*IS BAS(ALL_MAT)

*WHEN FLOW

*IS F_130

*REC(EXPRESSION = %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV]:0,FLOW = F_120)

*IS F_410

*REC(EXPRESSION = %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV]:0,FLOW = F_120)

*IS F_INV

*REC(EXPRESSION = [FLOW].[F_130]+[FLOW].[F_410]-%VALUE% > 0 ? [FLOW].[F_130]+[FLOW].[F_410]-%VALUE%:0,FLOW = F_120)

*ENDWHEN

*ENDWHEN

   

But the logic is calculating the addition flow considering the F_INV flow as zero for all the material classes.


On your query :

The logic  is calculating addition as below :

Suppose for  GL2001010

  Material          F_100        F_130      F_410        F_INV        F_120      F_999

         NA           10000                                           10000                       10000

       CS-EM                           500           20                                   520          20 

      CS-ED                             500           20                                  520           20

But we want :

  Material          F_100        F_130      F_410        F_INV        F_120        F_999

         NA           10000                                           10000                         10000

       CS-EM                           500           20                                    0             -500

      CS-ED                             500           20                                    0             -500


Other example with opening 0 :


  Material          F_100        F_130      F_410        F_INV        F_120       F_999

         NA               0                                                 0                                  0

       CS-EM                           500           20                                   520           20

      CS-ED                             500           20                                   520          20


Other example with negative opening  :



  Material          F_100        F_130      F_410        F_INV         F_120       F_999

         NA             -500                                                0                              -520

       CS-EM                            500           20                                   520           20

      CS-ED                              500           20                                   520           20

         



How can we achieve this.

Thanks in advance.

former_member186338
Active Contributor
0 Kudos

Ups, It's very hard to look on this long text, but I will try to simplify the question:

You have 3 parameters:

[FLOW].[F_130]

[FLOW].[F_410]

[FLOW].[F_INV]

And you want to check the expression: [FLOW].[F_410]+[FLOW].[F_130]-[FLOW].[F_INV] > 0 and if true - write the result to FLOW = F_120, if false - write zero to FLOW = F_120.


Is it correct?


Why do you marked question as answered?


Vadim

Former Member
0 Kudos

Yes, but the value in F_INV is in NA material class  and the values in F_130 and F_410 are in different material classes under the same GL.

So , I want this all aggregate values for F_130 and F_410 must be checked with F_INV with NA material class.

It was marked by mistake.

former_member186338
Active Contributor
0 Kudos

And to what material class do you want to write result?

Former Member
0 Kudos

with NA material class

former_member186338
Active Contributor
0 Kudos

Like this:

*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED

*XDIM_MEMBERSET P_DATASRC=MANUAL

*XDIM_MEMBERSET P_ACCT = BAS(1010300000)

*XDIM_MEMBERSET P_EMP_TYPE=NA

*XDIM_MEMBERSET P_PROJECT=NA

*XDIM_MEMBERSET P_SERVICES=NA

*XDIM_MEMBERSET P_CC=BAS(ABCD)

*XDIM_MEMBERSET P_ASSET=NA

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

//Preliminary calculation - sum F_130,F_410 for all materials

*XDIM_MEMBERSET FLOW = F_130,F_410

*WHEN P_MATERIAL

*IS * // BAS(All_MAT) - already scoped

*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410

*ENDWHEN

//Preliminary calculation - subtract F_INV for NA

*XDIM_MEMBERSET P_MATERIAL=NA

*XDIM_MEMBERSET FLOW = F_130

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV

*ENDWHEN

//Final

*XDIM_MEMBERSET FLOW = F_120

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120

*ENDWHEN

Vadim

Former Member
0 Kudos

Dear Vadim,

In your second section :

/Preliminary calculation - subtract F_INV for NA

*XDIM_MEMBERSET P_MATERIAL=NA

*XDIM_MEMBERSET FLOW = F_130

*WHEN P_MATERIAL

*IS * // NA - already scoped

*REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV

*ENDWHEN

Are your sure the scope is Flow = F_130

I guess it has to be F_INV

former_member186338
Active Contributor
0 Kudos

Yes, for sure F_INV, just a copy paste mistake Hope you've got an idea of script!

Test it!

Vadim

Former Member
0 Kudos

Yes, thank you vadim sir...

Its working very much fine.

One more query, how can I run logic for F_INV calculation in the same package.

Since in this scenario, I have to first transfer all my positive openings to F_INV, then only above calculation.

Is there any way , I can include the F_INV calculation in the same script.

since there are chances of user missing order of execution of package.

former_member186338
Active Contributor
0 Kudos

Yes for sure you can add before first preliminary calculation:

... 

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)

//Calculate F_INV

*XDIM_MEMBERSET FLOW = F_100

*WHEN FLOW

*IS * //F_100 - scoped

*REC(EXPRESSION = %VALUE% > 0 ? %VALUE% : 0,FLOW = F_INV , P_MATERIAL=NA)    

*ENDWHEN

//Preliminary calculation - sum F_130,F_410 for all materials

...

Don't use TIME loop - useless, and NEVER! use COMMIT with WHEN/ENDWHEN - useless and will reset the scope.

Vadim

P.S. It's better to close this discussion marking the correct answer!

Former Member
0 Kudos

I have tested 1 scenario , where opening value is negative , and so the minimum stock balance(F_410) value should be added to next period in order to consume(deduction flow)  it, but for this  I will  have to update my  logic for calculating the closing balances.

For example :

Opening    -100

Deduction    2000

Minimum Stock balance   200

As per our previous logic

F_INV   :   0

Addition   :  2200

Closing :   -100 + 2200 -2000 =  100

So we don't get the minimum stock ( 200) in next period.

I will post by closing balance calculation in a new thread.

Your inputs would be appreciated Sir!!

Thank you!!

former_member186338
Active Contributor
0 Kudos

Just please, always provide the table with member name and description, because you use both in text randomly. Very hard to understand!

Like:

F_410 - minimum stock balance

...

Vadim

Former Member
0 Kudos

ok Sir. .noted!!