Calculating Percentages for the Proportional Factors(APODPDANT) generated
We are working in an implementation project. We are calculating Detailed Proportional Factors for future by using previous one year corrected sales history.
Here in Demand Planning we are disaggregating Months to Weeks Time based Disaggregation by maintaining separate PA only for Weeks PA.
Once the Proportional Factors are generated in Months PA, we are copying the Cons Plan and Proportional Factors generated to Weekly PA where in this weekly PA both Proportional factors viewed in both Months and Weeks in separate data views.
As we maintained the Fiscal year variant(Fiscal Months and Fiscal Weeks) in Months PA, all Planning books are viewed and proportional factors are calculated based on Fiscal Months and the proportional factors disaggregated in Weeks PA into Fiscal Weeks.
Now we have a business requirement that the proportional factors disaggregated in Weeks PA into fiscal weeks. Have to calculate the percentage of each fiscal week and has to display into some other new keyfigure Proportional Factors (Percentages).
For Ex: Proportional Factors (APODPDANT) Generated W1 - 35, W2 - 10 , W3 - 30, W4 - 25 Items. Now in the new keyfigure Proportional Factors (Percentages) have to show w1 - 35%, W2 - 10%, W3 - 30%, W4 - 25%.
For achieving this I have written a macro which calculates the percentage W1 % = W1/ (W1+W2+W3+W4) * 100.
Here my problem is as we implemented the fiscal year variant there will be 5 weeks in 3rd month of every quarter.
how can I write the macro or to achieve this requirement. Requesting anybody faced this kind of business requirement in you previous experience, could you pls provide direction on this.