Avg forecast calc

[Former Member]

Hi,

I have a requirement to create a macro for calculating Average forecast. Avg forecast Calculation: sum of weekly qty / number of weeks.

For eg:-

W1	W2	W3	W4	W5
0	200	423	0	200

Number of weeks considered are from W2 to W5=4 weeks excluding the weeks which have no fcst & considering the week which have fcst till total horizon in defining the number of weeks.

Can you explain the steps for developing the macro?

Thanks,

Rajeev

[rajkj]

Hi Rajeev,

Please check the following pseudo code that might give you an idea on macro implementation.

Step 1: Initialize the variables (1 iteration)

- Use action element box

LAYOUTVARIABLE_SET( 'sum' ; SUM function for required horizon )

LAYOUTVARIABLE_SET( 'counter' ; 0 )

Step 2: Increment counter (1 .. n iterations)

IF ( ROW:FORECAST[i] > 0 )

  LAYOUTVARIABLE_SET ( 'counter' ; EVAL( LAYOUTVAR_VALUE( 'counter ' ) + 1 ) )

ENDIF

Step 3: Calculate avg of non-zero forecast values (1 iterations)

avg value = EVAL( LAYOUTVAR_VALUE( 'sum' ) / LAYOUTVAR_VALUE( 'counter' ) )

Thanks,

Rajesh

[Former Member]

Hi Rajesh,

Thanks for your reply.

For horizon calculation the macro needs to consider the wk having fcst to end of buckets in the planning book ignoring the initial wks having zero fcst. It will be great help if you share screen shot as i am stucked up at second step.

Thanks,

Rajeev