How to get key figure value at total row in detail all level

[Former Member]

Dear all,

In my planning book, at detail all level of Location, i want to get the key figure value only at total row.

Then i use the condition ACT_LOCATION = 'Total'.

My Planning book include key figure key1

In my macro i use auxiliary row "row_total" to get the key figure value.

This is my macro

IF ACT_LOCATION = 'Total'

row_total = key1

ENDIF

But it is not work.

Can you show me how to get the value of key figure in total row (at detail all level)?

Thanks.

[rajkj · Accepted solution]

Hi,

Please check the following pseudo code.

STEP 1: one iteration
/*Ensure you are at the aggregated level i.e. 'Total' level */
IF( AGG_LEVEL( '9ALOCNO' ) = 0 ) 
    DRILL_UP( '9ALOCNO' )
ENDIF
/* Copy the KF1 values to AUX KF */
AREA Aux_KF (column 1 - column N) = AREA KF1 (column1 - column N)

Thanks,

Rajesh

[Former Member · Accepted solution]

Hi Rajesh,

Thanks for your reply.

I understand your code. You want to go to the aggregate level and then get the key figure value.

But i want to get the key figure value in the detail level where the characteristic is total, into a auxiliary row. Then i will use this result to calculate the percentage of each characteristic in detail level.

Do you know how to solve this problem?

Thanks,

Bui Van Thai

[rajkj · Accepted solution]

Hi Bui Van Thai,

It looks like there is a gap in understanding the problem statement. Could you post a screenshot or give an example of the requirement? It will help to understand the issue better and provide appropriate suggestion.

Thanks,
Rajesh

[Former Member · Accepted solution]

Hi Rajesh,

Sorry because my question is not clear. I will show you exactly my trouble.

This is my scenario: In my planning table, i have 2 key figure KF1, KF2 with tow characteristic Location and Product.

This is the quantity value my table:

Location   Characteristic  KF1 (value)

0210          Total                1000

0210          APO_112          200

0210          APO_114          800

5210          Total                2000

5210          APO_112          500

5210          APO_114         1500

Now i have to calculate the percentage of each Product in each Location and save the result into KF2.

This is the result i want to get in KF2

Location   Characteristic  KF2 (value)

0210          Total                100

0210          APO_112          20

0210          APO_114          80

5210          Total                100

5210          APO_112          25

5210          APO_114          75

In fact i have many location, so would you tell my how to solve my trouble.

Thank you very much,

Bui Van Thai

[m_manimaran · Accepted solution]

Hi,

This can be achieved by the folowing

1.In your planning area, enter the calculation type of your KF2 as the P- Based on Dfferent Key Figure and set KF1 as the Disaggregation KF.

2. Write a macro to update value 100 in KF2 at the aggregated location level. If want, you can write a check with the KF1, if it is not equal to zero.

STEP 1: n iterations

/*Ensure you are at the aggregated level i.e. 'Total' level */

IF( AGG_LEVEL( '9ALOCNO' ) = 0 )

    DRILL_UP( '9ALOCNO' )

ENDIF

KF2 = 100.

So, the KF2 will be updated with the value 100 at aggregation level and the disaggregation will happen in the same manner as that of KF1.

Hope this will solve your purpose.

Regards,

Manimaran M.

[rajkj · Accepted solution]

Hi Bui Van Thai,

The requirement is bit complicated and use of aux key figure may not helpful as same levels of CVC apply. You need to use the following 3 macros as a collective macro.

Macro 1: Level change  /* Drill down locations and drill up the product */
IF(AGG_LEVEL( '9ALOCNO') = 1 )
   DRILL_DOWN( '9ALOCNO' )
ENDIF
IF(AGG_LEVEL( '9AMATNR' ) <> 1 )
  DRILL_UP( '9AMATNR' )
ENDIF
Macro 2: Capture the KF1 (total value)
VEC_LAYOUTVARIABLE_SET( 'KF1_TOTAL' ; 
  Area KF1(column 1 - column n) )
/*Drill down on material no. */
IF(AGG_LEVEL( '9AMATNR' ) <> 0 )
 DRILL_DOWN( '9AMATNR' )
ENDIF
Macro 3: Calculate percentages
Step 1: Area KF2(column 1 - column n) = VEC_DIV( Area KF1(column 1 - column n) ; 
                                                  VEC_LAYOUTVARIABLE( 'KF1_TOTAL' ) )
/* the above step needs to be analyzed for zero total values */
Step 2: 1 .. n iterations
Row KF2 = Row KF2 * 100

Thanks,

Rajesh

[Former Member]

Hi M Manimaran,

In my planning table, KF1 is key 9APSHIP Distribution receipt (planned).

In my planning area, i enter the calculation type of KF2 as the P-  and set 9APSHIP as the Disaggregation KF.

But when i save the planning area, there is an error "Key figure KF2: Invalid aggregation/disaggregation rule"

Do you know how to fix this error?

Thanks,

Bui Van Thai

[Former Member]

Thanks all,

By using your solutions, i have solved my problem.

Thanks