Macro

[Former Member]

Hi All,

I want to design a macro for Sales hist vs Fc comparison

Forecast(05.2016) to 04.2018 vs avg of 12 months of sales history(05.2015 to 04.2016)

forecast of particular month >avgsh(12 months) to 0-20% its yellow

forecast>avgsh(12 months) to 20-40% its brown

forecast>avgsh(12 months) to40+ its Red.

Cell has to highlight as per above conditions.

Appreciate for your inputs.

Thanks,

Naga.

[kenneth_snyder]

Amol,

Is Naga going to pay you?  Sounds like you are doing the work for him/her.

Ken

[Former Member]

Thanks for your kind comments. I tried to provide him all the hints but it end up in doing his job.

I hope it really helps him. Waiting for this confirmation.

Thanks

Amol

[Former Member]

Hi12

First you can calculate the avg  FCST or sales history for 12 months using AVG Function in macro.

You can calculate this and store in Auxiliary row.

Once you have this information, you can use IF, ELSE statements and compare with FCST key and change the CELL BG color accordingly.

Either you can create 3 different macros or 1 composite macro consisting all the above 3 conditions.

Please cross check from your side and let us know if this information helps you.

Thanks

Amol

[Former Member]

Hi Amol,

Thanks for ur reply.i want to compare forecast with avg of sales history.

Thanks,

Naga.

[Former Member]

Hi Naga

Yes then you can use the similar logic, first calculate the avg history of respective months i.e. 6 or 12 months into Auxiliary KF using Avg MACRO function and then you can compare this Auxiliary KF with sales order KF.

You can use this IF, ELse statement for comparison

If this information helps you, request you to close the thread following using below steps.

http://scn.sap.com/community/support/blog/2013/04/03/how-to-close-a-discussion-and-why

Thanks

Amol

[Former Member]

Hi Amol,

I have designed to till this. Please help me how can I design the remaining stuff.

Thanks,

Naga.

[Former Member]

Hi Amol,

I designed like this but am not getting result correctly  its saying below.

thanks,

Naga.

[Former Member]

Hi Naga

You want to change the color of CELL, so ideally it should be default macro and the background Macro. Please check the KF Final Forecast and see if you have selected change mode as "Attribute change".

Please go through the details about macro in the below link

Advanced Macros - Demand Planning - SAP Library

If this information helps you, request you to close the thread following using below steps.

http://scn.sap.com/community/support/blog/2013/04/03/how-to-close-a-discussion-and-why

Thanks

Amol

[Former Member]

Hi Amol,

I am not getting the desired result.

eg.,if avg sales is 200 for the months of 05.2015 to 04.2016 then forecast of 05.2016 is 220 it has to show green and more than 220 it has to be red.Please suggest.

Cells are highlighting from 04.2017 onwards and its not right way.

Thanks,

Naga.

[Former Member]

Hi my friend

I think it´s difficult to exactly right down the macro which you are looking for you.

I have given you all the hints which you can use to right the macro. Just another hint, you are talking about , I will first calculate the avg KF only and put into Auxiliary KF. Then I would use "MAPE" function to compare the both the KF and set the attribute accordingly.

Thanks

Amol

[Former Member]

Hi Amol,

Please suggest how can I use MAPE function.

Thanks,

Naga.

[Former Member]

Hi Naga

Please suggest you to please do search before you ask the quetsions.

Please find below the details how you can use the MAPE macro function.

MAPE()

MAPE( X1 ; ... ; Xn ; Y1 ; ... ; Yn ) returns the mean absolute percentage error for the values X1, Y1 through Xn, Yn. The arguments can be numbers that you enter inOperator/function dialog boxes, as well as cell(s), row(s),column(s) or area(s) from the planning table or the auxiliary table.

[Former Member]

Hi Amol,

Can you please suggest where I have to make the changes in the below macro to get the desired result.

Thanks,

Naga.

[Former Member]

Hi All,

Please suggest the changes for the above req'mt.

Thanks,

Naga.

[Former Member]

Hi Amol,

Can you please help me.

Thanks,

Naga.

[Former Member]

Hi Naga

As I told on this forum you can expect for the help and hints about the solutions but not providing the detail macro which you can copy/paste for your client. I think it´s not fare.

Please find below the sample, macro how you should calculate the Avg and MAPE functions.

I am sure now you should able to write the macro by your self. Please refer the 12M FCST KF by final FCST KF.

Request you to please cross check if this helps you and close the thread.

Thanks

Amol