Macro to calculate Percentage

[Former Member]

Dear APO Experts,

I have the following requirements:

We have 4 Regions: North, East, West & South.

1)Need to calculate average of the History of EACH Region of last 6 months(This can be achieved using simple macro).

Based on region average need to calculate the percentage of each each region.(How to calculate percentage of the each region.Formula = Each Region Value* 100/ Total of All Regions)

(I can use proportional factors to generate proportions but issue is we need to calculate the percentages even for Manual Adjustments also for this system should generate Percentage dynamically. which is not possible if i generate proportional factors )

2) We have the Confirmed Supply Data for each month.

scenario: For the Month March 2011 Confirmed Supply = 1000 Units (1000 Units will load into the Planning area at the level of Material and Plant),We do forecast at Region Level.

When i drill down at regional level, supply plan also gets spitted to region level (Since DP stores data at CVC level(Lowest Level).

My Requirement is When planner drill downs at all region level system should hide details of the supply plan at region level it means it should display at totals level of the supply plan key figure (I don't know what would be the impact if planner selects only 2 regions in the Selection ID an d load the data)

Anyways i want to display Supply plan at total level in the Planning book even i drill down at 4 regions or 2 region or 1 region.(Please correct me if my understanding is wrong)

Please help

BR

katerine

[thomas_schulze2]

Hello

for 1) in case you have manual adjustments to consider I would introduce a new keyfigure which represents your "relevant history" (being calculated based on the native history shipments and the manual adjustment keyfigure - thats a simple macro). The prop factors would use the new keyfigure "relevant history" instead of "history".

Means, your Planning Book could have 4 keyfigures

- history

- adjustment

- relevant history

- 6month average history

For the "dynamic" proportion I wonder why you can't simply use the abs/rel switch over in planning book. It does show % based on what you have loaded to the planning book. Proportional factors are only meaningful in case you want disaggregate other keyfigure based such factors.

for 2) this is possible but not trivial: write a macro using DET_LEVEL or AGG_LEVEL and check if user has performed a drilldown to region -> if yes - hide the keyfigure supply plan in planning book using ROW_VISIBLE but execute this macro on "all planning objects" or "certain levels" (use LEVEL1 after first drill-down). This should leave the Total of Region in the planning book and hide the details for keyfigure "supply plan". In addition I wonder if you want to change the disaggregation behaviour of the keyfigure supply plan (put to "no disaggr").

Regards

Thomas

Edited by: Thomas Schulze on Mar 21, 2011 12:11 PM