DP- Macro for drill down calculation

[Former Member]

Need help with creating a macro for the following :

Planning book/ data view:

	Product	Wk1	Wk2	Wk3
Sales Qnty	100	10	30	5
	101
	102	20	40	20
	103
	104

Macro should drill down at product level and when ever the product is “100” it should do the following calculation:

Sales of Product ( 100) for Wk1 + Sales of Product (102) for wk1= 30

Find % difference for product 100 for wk1 with the total = (10/30) *100= 33.3

Generate alert if the % difference is NOT between 40 and 50. This should be done in same way for other weeks. Calculation is only required when the product is “100” in drill down.

I can use DRILL_DOWN( 'product' ) for drill down. I not sure what functions can be used for the calculation.

[rajkj · Accepted solution]

Here is a simple procedure -

• Use SUM_CALC macro function to capture the total value (for instance sales quantity of all products) for each time period at aggregate level and assign this value to a layout variable.
• Then use drill down function on product
• If the product is at detailed level and equal to 100, carry out computing the ratio i.e. sales quantity/layout variable value *100
• Trigger an alert based on the ratio value and your business logic

[Former Member · Accepted solution]

Thank you very much Rajesh for the helpful response.

I am sorry that my table snapshot omitted some information. Some of other products which are not required in calculation also has quantities and only want to consider product “100” and “102” in the calculation.  SUM_CALC macro function seems to add all products.

In the following example , I need to ignore product “104 “, “103” and “101” quantities.

	PROD	Wk1	Wk2	Wk3
Sales Qnty	100	10	30	5
	101	20	13	30
	102	20	40	20
	103		40
	104	50	30	20

Will you please review the pseudo code and let me know if this is going to work. I would really appreciate corrections and any help on syntax as am new to this .

-------------------------------------------------------------------------------

DRILL_DOWN( 'PROD ' )

IF

ACT_IOBJNM_VALUE( ' PROD';  ) = 100

LAYOUTVARIABLE_SET( 'P100' ; Sales Qty KF row value )

END IF

IF

ACT_IOBJNM_VALUE( 'PROD';  ) = 102

LAYOUTVARIABLE_SET( 'P102' ; Sales Qty KF row value )

END IF

LAYOUTVARIABLE_SET( 'diff' ; EVAL(( LAYOUTVAR_VALUE( 'P100' ) /       

( LAYOUTVAR_VALUE( 'P100' ) + LAYOUTVAR_VALUE( 'P102' ) * 100  ) ))))

IF LAYOUTVAR_VALUE( 'diff' ) NOT BETWEEN 40 AND 50

Update in Context : % difference is [ Warning ]

LAYOUTVARIABLE( 'diff' )

END IF

  -------------------

[rajkj · Accepted solution]

Hi JD,

I don't think the macro function ACT_IOBJNM_VALUE will give you the expected result if you load multiple PLOBS and drill down on a specific characteristic. You can try the following alternative -

1. Define another time series key figure KEYF1

KEYF1 can be assigned 0 or 1 i.e. if product is either 100 or 102, KEYF1 value is 1 otherwise 0

2. Define one macro to be executed when you open the dataview

INITIALIZE VARIABLES (macro assigned to start event)

STEP 1

     LAYOUTVARIABLE_SET( 'sum' ; 0 ) // 1 iteration

STEP 2

     AuxRow1 = 0  // all iterations

3. Drill down to sum the values of prod 100 and 102

COLLECTIVE MACRO  - 1

DRILL DOWN

SUM

4. Drill down and compute the ratio for prod 100 and 102

COLLECTIVE MACRO  - 2

DRILL DOWN

RATIO

DRILL DOWN Macro

--------------------------------------

STEP 1   // 1 iteration

IF

AGG_LEVEL( 'PROD' ) = 0

DRILL_UP( 'PROD' )

ENDIF

DRILL_DOWN( 'PROD' )

SUM

-----------------------------------

STEP 1      // all iterations

IF

ACT_LEVEL <> 0

IF

KEYF1[t] = 1

LAYOUTVARIABLE_SET( 'sum' ; ( LAYOUTVAR_VALUE( 'sum' ) + SALESQTY[t] ) )

ENDIF

ENDIF

RATIO

-----------------------------------

STEP 1      // all iterations

IF

ACT_LEVEL <> 0

IF

KEYF1[t] = 1

AuxRow1[t] = EVAL( ( SALESQTY[t] / LAYOUTVAR_VALUE( 'sum' ) ) * 100 )

IF

AuxRow[t] between 40 and 50

Update Context .........

      AuxRow[t]

ENDIF

ENDIF

ENDIF

Thx,

Rajesh