Former Member
Apr 03, 2012 at 08:22 AM

# How to write Macro to Calculate Average of Forecast

82 Views

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