APO SNP Macro to calculate average of keyfigures until end of the month

[former_member727941]

Hi APO Experts - Hoping that you can guide me if there is a way to use standard APO SNP macro for our requirement

I need to start my calculation on Day15 with reference to current day. I need to get the average of sum of forecast and sales order per month and store the value to all bucket belonging to the same month.

Example:

Assuming today is Jan1, then I need to get the average of sum of forecast and sales order from Jan15 until Jan31 and that will be the value of my new KF1 from Jan15 to Jan31. Value of my new KF1 from Feb1-28 will be the average of sum of forecast and sales order for the whole month of February.. Then I need to run the same calculation for the succeeding months.

Here are my challenges

1. How will I write the macro that will compute the average of sum of sales order and forecast from D15 until end of the month?

2. Once I get the average for the current month (D15-last day of the month) how am I going to store it to KF1 from D16 to D(last day of the month) if I am running the macro everyday. For example, when I run the macro tomorrow, then D16 will now be the 15th day so my new average should be D16 until last day of the month and I need to store that KF from D15-D(last day of the month).. So the last bucket to store [last day of the month] should not change.

Thanks for your help and time

[Former Member]

Hi Mylene, might not be a very clean/efficient solution but could work. You could find the beginning and end of any month using the macro function MONTH. When the value of MONTH changes you know you are in a new month. Store the month values in an auxiliary KF in the first bucket of the month; only for the current month store the current month's value in the 15th bucket rather than the first bucket of the month. (I am assuming that you have daily time buckets.) To find the 15th day from today you could use the check DAY(Bucket date) - DAY (TODAY) = 15.

So using a Layout variable let's call it CURMONTH you can keep on checking the month of the bucket and once the month of the bucket changes, store the value of the month in the first bucket of the month (or 15th day from today) and also store the value of the month in the variable CURMONTH which you would use for comparison.

In another Auxiliary Key Figure keep on adding the value of the forecast and Sales during the month (from first relevant bucket of the month till the month changes). Also have an variable to store the number of the days of the month to be used for average. I think you might get the picture now. Keep on cumulatively adding the Sales and forecast from beginning of the month till the month changes. Once the month changes divide the cumulative sum of Sales and forecast with the number of relevant days to get the average. Store this in the first bucket of the new month in your relevant KF.

You would now need to have another step in the macro with processing direction backward. At the beginning of every month you already have the average value for the previous month. During the backward processing copy the average value to the month before till your bucket changes. Stop the copying when you hit the 15th day from today.