Macro to calculate 'average'

[former_member566355]

Hi,

Scenario is as below :-

Planning Bucket is ....7 days in future and 12 months in past.

User will enter the number of months (variable) which should be used to calculate the average.

e.g. if user enters '5' average of last 5 months should be calculated and put it on the current day. 

The user input for the number of months will be a separate KF. The value will be entered on the current date bucket.

Please let me know the functions/syntax to be used for the same.

Regards,

RS

[rajkj]

Hi RS,

You can use vector macro functions like VEC_LAYOUTVARIABLE_SET and
VEC_LAYOUTVARIABLE to manage your requirement.

//Initialize a vector variable with KF1 data

VEC_LAYOUTVARIABLE_SET( 'vec1' ; Area[ KF1] ) //consider passing entire past key figure data

//Define an aux key fig row and initialize it for entire past date range

Aux KF = 0 // for the entire past date range

//Initialize another vector variable with aux key fig data

VEC_LAYOUTVARIABLE_SET( 'vec2' ; Area[ Aux KF] ) 

//Capture number of months data (i.e. KF2 value)

LAYOUTVARIABLE_SET( 'num' ; KF2 row value)

//Capture past data horizon

LAYOUTVARIABLE_SET( 'past_hori' ; macro function NUMBERA ) //use Function NUMBERA and set date range to past

//Capture past hori - user value

LAYOUTVARIABLE_SET( 'diff' ; EVAL( LAYOUTVAR_VALUE( 'past_hori' ) - LAYOUTVAR_VALUE( 'num' ) ) )

IF LAYOUTVAR_VALUE( 'diff' ) > 0

//Initialize another vector - First segment

VEC_LAYOUTVARIABLE_SET( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec2' ; 0; LAYOUTVAR_VALUE( 'diff' ) )

//Append the area based on KF2 value - Second segment

VEC_LAYOUTVARIABLE_APP( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec1' ; LAYOUTVAR_VALUE( 'diff' ) + 1 )

// Use sum function to get the sum of values

LAYOUTVARIABLE_SET( 'sum' ; SUM( Aux KF ) ) // for the entire past date range

Avg value = EVAL( LAYOUTVAR_VALUE( 'sum' ) / LAYOUTVAR_VALUE( 'num' ) )

ENDIF

http://help.sap.com/saphelp_SCM700_ehp02/helpdata/en/4b/755bee3bf75a18e10000000a421937/frameset.htm

Thanks,

Rajesh

[former_member566355]

HI Rajesh,

Thanks for the detailed reply. I am still a bit unclear on the way the logic is flowing through the steps specially from the step of 'diff' calculation

i.e.

//Capture past hori - user value

LAYOUTVARIABLE_SET( 'diff' ; EVAL( LAYOUTVAR_VALUE( 'past_hori' ) - LAYOUTVAR_VALUE( 'num' ) ) )

Could you please clarify the steps that you have given w.r.t '12' months past horizon and user input is '5' ?

For your info, I am giving some of the doubts that I have?

//Capture past hori - user value

LAYOUTVARIABLE_SET( 'diff' ; EVAL( LAYOUTVAR_VALUE( 'past_hori' ) - LAYOUTVAR_VALUE( 'num' ) ) )    what is the necessity for calculating the difference ?

IF LAYOUTVAR_VALUE( 'diff' ) > 0

//Initialize another vector - First segment

VEC_LAYOUTVARIABLE_SET( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec2' ; 0; LAYOUTVAR_VALUE( 'diff' ) )      does this assign range '0' to '7 (12 - 5) to vec2 ?

//Append the area based on KF2 value - Second segment

VEC_LAYOUTVARIABLE_APP( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec1' ; LAYOUTVAR_VALUE( 'diff' ) + 1 )   What is second segment ?

// Use sum function to get the sum of values

LAYOUTVARIABLE_SET( 'sum' ; SUM( Aux KF ) ) // for the entire past date range

              Sum should be for 5 months or the entire past date range ?

Avg value = EVAL( LAYOUTVAR_VALUE( 'sum' ) / LAYOUTVAR_VALUE( 'num' ) )

Last one :- all the activities will be different 'STEPS' ?

Thanks,

RS

[rajkj]

RS,

Going by same example of past horizon as 12 months and you need to calculate the sum of 5 months alone (this value is stored in the second key figure), lets assume

Aux KF row = 0 (from month 1 to 12).

We need to fill this key figure with data from 8th month to 12th i.e. latest 5 months.

The vector functions described in the earlier post help you to achieve this.

• First segment to copy the values (i.e. 0) of all cells of Aux KF from 1 to 7 to vector variable. Vector = 0 (from 1 to 7 months). To get this segment, we need the layout variable 'diff'.

• The second segment captures the actual values of KF1 from month 8 to 12.

      Vector {from mon 1 to mon 12} = Vector {from mon1 to mon 7} + KF1 {mon 8 to mon 12}

• The sum function should be executed for the entire past range on aux key figure row which is nothing but sum of 5 months of KF1 values.

• All these can be accommodated in one step

Addendum:

//Append the area based on KF2 value - Second segment

VEC_LAYOUTVARIABLE_APP( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec1' ; LAYOUTVAR_VALUE( 'diff' ) + 1 )

//Assign tailored vector to aux key figure.

Area (Aux KF) = VEC_LAYOUTVARIABLE( 'vec3' )

// Use sum function to get the sum of values

LAYOUTVARIABLE_SET( 'sum' ; SUM( Aux KF ) ) // for the entire past date range

Avg value = EVAL( LAYOUTVAR_VALUE( 'sum' ) / LAYOUTVAR_VALUE( 'num' ) )

Thanks,
Rajesh

[former_member566355]

Hi Rajesh,

Thanks again for the in-depth clarification,

I have tried it in the system, but there is some syntax error. Giving below the macro which I wrote in the system. Could you please let me know if I am missing something ?

1. Macro Name

1.1 - step1

   1.1.1 action box

        operator/function - vec_layoutvariable_set ( 'vec1' ; area [ $KF1 (M1) ; $KF1 (M12)] )

1.2 - step2

   1.2.1 action box

        operator/function - vec_layoutvariable_set ('vec2' ; area [ $KF2 (M1) ; $KF2 (M12) ]

1.3 - step3

   1.3.1 action box for number

         operator/function - layoutvariable_set ( 'num' , row : KF2 (from M1) )

  1.4 - step4

    1.4.1 action box for past months

      operator/function - layoutvariable_set ( 'past_months' ; numbera [$KF2 (M1) ; $KF2 (M12) ] )

  1.5 - step5

    1.5.1 action box for difference

operator/function - layoutvariable_set ( 'diff'' ; eval (layoutvar_value ( 'past_months') - layoutvar_value ('num') )

  1.6 - step 6

    1.6.1 IF ( control statement )

    1.6.2 condition

             1.6.2.1  function/operator layoutvar_value ('diff'') > 0

    1.6.3  action box

             1.6.3.1  function/operator vec_layoutvar_set ('vec3' ; vec_layoutvariable ( 'vec2' ; 0 ; layoutvar_value ('diff') ) ) )

    1.6.4  action box

              1.6.4.1  function/operator vec_layoutvar_app ('vec3' ; vec_layoutvariable ( 'vec1' ; layoutvar_value ('diff') + 1 )  )

    1.6.5  action box

             1.6.5.1  function/operator layoutvariable_set ('sum' ; sum ( $KF2 (M1) ; $KF2 (M12) ) )

    1.6.6  results cell : KF1 (todays date)

            1.6.6.1  function/operator layoutvar_value ( 'sum' )  / layoutvar_value ('num')

     1.6.7 ENDIF

The number indexing is given for your reference. The addendum that you have given havnt added as of now in the macro which I ll do.

I have 2 doubts here :-

1. In which KF the user will enter input number of months ? and on todays date or 1st past bucket ?

2. In the syntax that you have given -

//Append the area based on KF2 value - Second segment

VEC_LAYOUTVARIABLE_APP( 'vec3' ;  VEC_LAYOUTVARIABLE( 'vec1' ; LAYOUTVAR_VALUE( 'diff' ) + 1 )

      for the function VEC_LAYOUTVARIABLE the std syntax is ('vector', from value, to value) ...only one value is maintained after 'vec1' ? 

Thanks in advance.

RS

[former_member566355]

Hi Rajesh,

Thanks a lot....I am able to create the macro without syntax errors.

But the calculations are not taking place in the planning book (no actions when I execute the macro)

I am checking it. Trying to find out if there is anything to be looked into when using 'vector' functionality.

Regards,

RS

[rajkj]

Hi RS,

This macro is bit tricky and you need to be careful with dates. Probably, screenshots of your macro and data view will be helpful to find out the mistake.

Thanks,
Rajesh

[former_member566355]

Hi Rajesh,

I am able to complete the macro finally. It was achieved with minor changes.

1. I did not initialize vector 2 for auxilliary KF (i.e. KF2).  So ther was no need to append two segments to vector 3.

2. Vector 3 was SET using values 'diff+1' onwards from vector1 (i.e. vector for 1st KF). This gives the required KF1 values for summing up.

REAL thanks to you.

RS