How to write Macro to Calculate Average of Forecast

[Former Member]

Dear SAP Experts,

My scenario is:

- At the beginning of each Quarter, I run forecast for each Moth of that Quarter and save to Key Figure “Forecast”. I view results by Moth.

Fore example:

            + In Quarter 3 of 2012, I run forecast for each Moth 07/2012, 08/2012, 09/2012.

+ In Quarter 4 of 2012, I run forecast for each Moth 10/2012, 11/2012, 12/2012

- Then, I want to calculate the Average of forecast in a day of a Quarter. Formular is:

Average of a of a day in a Quarter = (Forecast of First Month + Forecast of Second  Month + Forecast of Third Month)/Number of Working days in a Quarter

Fore Example,

Average of a day in Quarter 3/2012 = (Forecast of 07/2012 + Forecast of 08/2012 + Forecast of 09/2012)/Number of Working days in a Quarter

The “Number of working day in a Quarter” is a Field which is defined in a my own created table in the Database (table “Parameters”). I assume all Quarter have the same Working days (For example, 66 days).

The Average value is stored in Key Figure “Average”. This Key Figure is also viewed by Month. But in this case, all Months in the Quarter 03/2012 contain the same value, all Months in the Quarter 04/2012 contain the same value.

The layout of my table is as following:

                        |M 07.2012| M 08. 2012   | M 09.2012       | M 10.2012       | M 11. 2012 | M 12.2012 |

Forecaset         | 10000000| 11000000     | 12000000        | 13000000         | 14000000   | 15000000  |

Average            | 500000    | 500000        | 500000            | 636364            | 636364       | 636364     |

My Questions are:

1.   1. I think that firstly, I need to write a Macro to read value of Field “Number of working day in a Quarter” from table “Parameters” and assign this value to a Global variable (or an Auxiliary Cell).

If this is the case, How to write this Macro?

2.    2. How to write Macro to calculate the Average of a Quarter as above formular and requirements?

Thanks very much!

Duyennx

[rajkj · Accepted solution]

Hi Duyen,

Assumption: Your data view's time bucket profile contains only months.

1. You need not to maintain any table for retrieving working days in a quarter. Macro function, BUCKET_WDAYS gives you that data. The details are as follows.

BUCKET_WDAYS( <calendar> ; M1 ; M2 ; M3 ) returns the number of workdays in the periods given by the cell(s), row(s), and/or column(s) (not areas) M1, M2, and M3. In the attributes of the argument elements, you must specify Period data as the data source. The specification of a calendar as the first argument is optional. If you do not specify a calendar, the system reads the calendar given by the time stream in the storage buckets profile. If no time stream is defined, the system uses calendar days.

2.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr. Rajesh,

Thanks very much for your very very helpful Answer.

I have some further questions. Please help me:

1. Incase I need to save data in Field “Number of working day in a Quarter” in my own created table "Parameters". Can I write Macro to read and assign value from this Field to a Global variable (or an Auxiliary Cell)? If yes, how to write?

Actually, I need to know the ability of Macro: can it read data from a table in Database. Because in the future, I may need to maintain some Global Parameters in a my own created table.

2. Can you change the Macro so that it can run for all Quarter? Currently, your Macro only run for the current Quarter. If today, I want to run forecast for Quarter 03/2012 (include: month 07/2012, 08/2012, 09/2012), your Macro does not work. In order to make this Macro work, I have to use Tcode /sapapo/sdp8b --> Tab Dataview and  set the Field Planning Start of the Dataview to 01.07.2012. But, the requirement is that: user should not have to change this Field, Macro should run automatically.

Thanks very much for your support!

Duyennx

[rajkj · Accepted solution]

Hi Duyennx,

1. You can use either function module macro or user exit macro (both involves customization) to interact with your custom table from a planning book.

User Exit macro:

https://service.sap.com/sap/support/notes/418801

https://service.sap.com/sap/support/notes/1119651

Custom macro function:

- Go to Macrobuilder, in the second or third menu you will found the User function option. Go in there and type the name of the FM that you want to create.

- Go on and in the parameters screen select the SDP_PlanningBook and the SDP_Dataview. Then select the type of value you want as  output (Char or integer).

- Create the FM using the standard transaction SE37. To know the correct parameters you can use as a template the FM /SAPAPO/DET_LEVEL or any other standard function module.

- Then in order to use the FM in the Macrobuilder, add an Operator/function and you will see it in the listbox.

- To know how the system transfer the parameters from the Macro to the FM, you can add a break-point in the FM.

2. The screenshot is just an example. You can run the macro for all quarters, but it requires a check.

Step1: 1 iteration (i.e. starting time bucket)

Action box macro element

     LAYOUTVARIABLE_SET( 'QUARTER1' ; '0' )

Step2: For entire horizon (i.e. all time buckets)

Action box macro element

     LAYOUTVARIABLE_SET( 'QUARTER2' ; QUARTER( BUCKET_BDATE(

           Row: Forecast     //your row element and change the data source to column attributes

       ) ) )

IF( LAYOUTVAR_VALUE( 'QUARTER1' ) <> LAYOUTVAR_VALUE( 'QUARTER2' ) )

    Action box element

     LAYOUTVARIABLE_SET( 'QUARTER1' ; LAYOUTVAR_VALUE( 'QUARTER2' ) )

ENDIF

For simplicity and save time, I reused my test macro implementation. In your case, it should show the current month rather than the current date.

Thanks,
Rajesh

[Former Member · Accepted solution]

Dear Mr. Rajesh,

Thanks very much for your quick and helpful answers.

I will test and if I have further questions, Pls help me.

Thanks very much!

Duyennx