build macro with disagragation key figure

[Former Member]

Dear Experts!

I am performing currency forecasting. I the forecast result is display in "forecast" key figure. I configure 1 Data view with 2 key figures

- Forecast: display the forecast result in value (VND)

- Key figure convert from value to quantity (PC)

I only forecast in value. Then I want to convert from value to quantity by using Macro with formmula: Quantity = Sum (values/denominations)

How can I build macro in the system

Thanks!

[former_member187488 · Accepted solution]

Hello,

I don't understand your formula well ....

For example, you have the following result from forecast, saved key figure for values:

period 1 - 10

period 2 - 20

period 2 - 15

What do you expect for key figure 2, the quanity key figure?

And where do you get the denominations? From product master?

Thanks for the information.

Best Regards,

Ada

[Former Member · Accepted solution]

Hi Ada,

E.g: I forecast for 3 Materials (Products): DL2055 (500.000 VND currency); DL1054 (50.000 VND currency) and DL1013 (1.000 VND currecy). ==> I have a characteristic: APO Product with 3 above values

Forecast reruslt is saved in value forecast key figure. E.g: after forecasting an drill down, I have result as following:

- DL2055: 2.000.000

- DL1054: 1.500.000

- DL1013: 1.000.000

Now I want to convert to the key figure 2, that is saved in quantity with formula:

Quantity forecast = forecast result for DL2055/500.000 + forecast result for DL1054/5.000 + Forecast result for DL1013/1000.

How I build this in the system by using Macro.

Thanks

[abhay_kapase · Accepted solution]

Hi Legiang,

Here is a simple suggestion. See if it suites you.

First of all you have the forecast in KF Forecast:

e.g.

Model Forecast

- DL2055: 2.000.000

- DL1054: 1.500.000

- DL1013: 1.000.000

Now, for converting it into qty, you can utilize one of the fields from product master.

e.g. in the product master you can save these currency values in additional fields available in Properties Tab

DL2055 (500.000 ); DL1054 (50.000) and DL1013 (1.000)

Now, you can utilize this field from product master to convert it into quantity.

e.g.

You can use following syntax to read that value stored in product master

MAT_C( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION ) Where ATT01 is that field value.

Your formula to calculate qty = Forecast KF/ value in ATT01 field

Revert if this solution is applicable to you.

Regards,

Abhay Kapase

[former_member187488 · Accepted solution]

Hello,

The values of key figure is saved for each planning object, in your sample, for each product.

And also, macro is executed on each planning object one by one.

Now you want to calculate a value based on the three planning objects, and save it to key figure 2 ...

I think that maybe you'll need another key figure (key figure 3) to save the conversion factors.

For example:

KF1 : Product 1: 2000

Product 2: 1500

Product 3: 1000

KF3: Product 1: 500

Product 2: 5

Product 3: 1000

Then build a macro to be executed on detailed level of product:

KF2 = KF1/KF3

And the total value of KF2 is the result you want.

What do you think of this?

Best Regards,

Ada

[Former Member · Accepted solution]

Dear AbhayKapase!

Thanks for your ansewer. This is helpful for me, and I hope you can write your answer steps by steps. So I can Practise in the system.

Thanks for your help once more.

[abhay_kapase · Accepted solution]

Hi Legiang,

Here is the concept once again.

We are trying to utilize the additional fields available in the product master

to store the value, which we can later utilize in the macro. This is standard SAP functionality.

1) Go to Product master in Properties tab, if you scroll down there are additional fields available.

You can save the value there for your three products.

DL2055 (500.000 ), DL1054 (50.000) and DL1013 (1.000)

2) Create a Macro with steps like this:

First you need to read the value from the product master which you can do by using below syntax

MAT_C( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION ) Where ATT01 is that field name. (There are various syntax available to read this value. I have given this as an example, you can choose a different one also)

It will return ATT01 values as 500, 50 and 1 respectively for yor models.

You can choose save these values in auxillary Cell.

3) Now, the formula for calculation of qty will be like this.

Formula to calculate qty = Forecast KF/ value in Auxillary cell.

Benefits of this method is that it is easy for maintainance. Let say in future for DL2055 value changed to 600 from original 500.

What all you need to do it to go to product master and change the value.

You now need to try this in system. It should not be difficult with the above concept.

All the Best!!

Regards,

Abhay Kapase

Please close the thread if you have got answer to your question

[Former Member · Accepted solution]

Hi Ada!

Your Idea is good, but when applying in my case, I see the following problems:

- I use many periods in my forcasting. If I use KF3, I have to put denominations (500, 50, 1) in each period. So It will waste of time and I have to keep putting in the future

- On the contrary, I use KF1 is quantity KF and KF 2 is value KF. So, the formula is KF2 = KF1*KF3. And the number in the KF2 is very large. When I use the Drill down funtion and choose "drill all" ==> a massage appear "value from ... to.. is too larger" and then the figure is disappear. Same situation appear when I put a large figure to other KF. Even If I Put large number, I cannot save==> How can I solve this problem?

[former_member187488 · Accepted solution]

Hello Legiang,

It denpends on you how you would like to get the factor. Of course you can make it a field in product master.

Regarding the large number, it is a limitation in the liveCache, so we cannot fix this at application side.

It is suggested to use a 'larger' UOM for the planning area, like if you originally use KG, you can consider UOM 'TO' ...

Best Regards,

Ada

[Former Member · Accepted solution]

Dear Abhay!

I clearly understand you concept. What I confuse now is how to save value to auxillary cell?

Thanks!

[abhay_kapase · Accepted solution]

Hi Legiang,

In Macros, we have a functionality called as Auxillary KFs row, where we can store the values temporarily(locally).

On the similar lines you can define a auxillary cell.

Regards,

Abhay Kapase

[Former Member · Accepted solution]

Hi Abhay!

In the step 2, I build macro: auxillary cell = MAT_C( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION )

And in the step3, how can I call value in auxillary cell. So that, I can build macro fromula: quantity KF = Forecast KF/value in auxillary cell

Thanks!

[abhay_kapase · Accepted solution]

Hi Legiang,

If you have defined a Auxillary cell and completed step 2. You already have that value saved in the auxillary cell. You just have to use it in the formula.

Now, you define formula in third step

Qty KF = Forecast KF/Auxillary field

Regards,

Abhay Kapase

[Former Member · Accepted solution]

Hi Abhay!

In the Step 2, I build macro like this:

- Macro 1

- Step 1

- Row: auxillary cell =

- Operation/Funtion: MAT_C( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION )

In the step 3, I intend to create Macro like this:

- Macro name: Macro 1

- Step 1

- Row: Quantity KF =

- Row: Forecast KF

- Operation/funtion: /

- Row: Auxillary cell

And My difficulty is: I can't drap and drop auxillary cell from element window to macro in the macro book window.

What is your idea?

Thanks!

[m_manimaran · Accepted solution]

Hi,

Please try to use LAYOUTVARIABLE_SET() instead of auxiliary cell. In the first step, read the value from the product master and in the second step, you do the calculation.

Macro 1

- Step 1(1 Iteration: 01.01.2001;01:01.01.2001) ************Use one iteration only**********

Action Box-

- Operation/Function: LAYOUTVARIABLE_SET ('DENOMINATION' ;

- Operation/Function: MAT_C( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION )

- Operation/Function:)

- Step 2 (120 Iterations: 01.01.2001;01:01.01.2011)************Use required iterations**********

- Row: Quantity KF =

- Row: Forecast KF

- Operation/funtion:/

- Operation/Function: LAYOUTVAR_VALUE ('DENOMINATION')

Hope this will work.

Regards,

Manimaran M.

Edited by: M Manimaran on Aug 9, 2011 8:39 AM

Edited by: M Manimaran on Aug 9, 2011 8:43 AM

[abhay_kapase · Accepted solution]

Hi Legiang,

Idea of Manimaran is good. You can use LAYOUTVARIABLE_SET() .

Please try this.

Regards,

Abhay Kapase

[Former Member · Accepted solution]

Hi Mamimaran!

I have followed your instrution and build macro suceessfully. But the system doesn't work. I see no figure in quantity KF.

Is there any thing wrong?

Thanks!

[m_manimaran · Accepted solution]

Hi,

One mistake in the macro. We should not use MAT_C. We should use MAT only. Because MAT_C will read the value as the character type. MAT only read the value as numerical. Try this.

Also please check you updated the product masters with your denomination values in the ATT01 field?

Regards,

Manimaran M.

Edited by: M Manimaran on Aug 9, 2011 10:35 AM

[Former Member · Accepted solution]

Oops!

Although I have checked additional feild in the product master to make sure the denominaitons has put to ATT01 feilld and change macro syntax to MAT(); There is no figure again.

Regards!

Legiang

[m_manimaran · Accepted solution]

Hi,

can you please provide all the steps of your macro here . I will analyze and see what is wrong?

Regards,

Manimaran M.

[Former Member · Accepted solution]

Hi

Macro I build as following:

Macro1

Step 1 : ( 36 Iterations : M 09.2009; M 08.2012 )

New action box

- Operator/Funtion: LAYOUTVARIABLE_SET ('DENOMINATION' ;

- Operator/Funtion: MAT( 'ATT01' ; ACT_PRODUCT ; ACT_LOCATION )

- Operator/Funtion: )

Step 2 : ( 36 Iterations : M 09.2009; M 08.2012 )

Row: Conversion from quantity to value ( Frm M 09.2009 ) =

- Row: Forecast quantity ( Frm M 09.2009 )

- Operator/Funtion: *

- Operator/Funtion: LAYOUTVAR_VALUE( 'DENOMINATION' )

( In this scenario, I forecast in quantity KF and want to convert to value KF, So I use Operator/Funtion: *)

Pls check!

Thanks

[former_member583013 · Accepted solution]

Hi,

Instead of macro function MAT use macro function MATLOC_EXTRA().

In your case it should be MATLOC_EXTRA( 1 ; ACT_PRODUCT ; ACT_LOCATION ) .

Regards,

Sunitha

[former_member187488 · Accepted solution]

Hello,

Sunitha is right.

Just to add, for the first step, you don't need 36 iterations. One is enough.

Best Regards,

Ada

[m_manimaran · Accepted solution]

Hi,

Yes, Sunitha and Ada are correct. Please make the changes like this and try.

Macro1

Step 1 : ( 1 Iteration : M 09.2009; M 09.2009)

New action box

- Operator/Funtion: LAYOUTVARIABLE_SET ('DENOMINATION' ;

- Operator/Funtion: MATLOC_EXTRA( 1 ; ACT_PRODUCT ; ACT_LOCATION )

- Operator/Funtion: )

Step 2 : ( 36 Iterations : M 09.2009; M 08.2012 )

Row: Conversion from quantity to value ( Frm M 09.2009 ) =

- Row: Forecast quantity ( Frm M 09.2009 )

- Operator/Funtion: *

- Operator/Funtion: LAYOUTVAR_VALUE( 'DENOMINATION' )

It will work.

Regards,

Manimaran M.