Macro - Calculating Average Value

[former_member566355]

Hi,

The requirement is as follows -

There are 2 key figures X1 and X2. The average for past months is to be calculated for KF 'X1'.

Now the number of months to take into account depends on the value user enters in KF 'X2'.

Could you please let me know the syntax to be used ?

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