Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Creating latest estimate, problem with merging categories

Hi Experts,

I'm trying to create a script to merge ACTUAL data with the FORECAST data into a selected FORECAST category. The merging of data should depend on the first forecast month of the selected forecast category. Please refer below for the matrix:

		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
JANFCST		F	F	F	F	F	F	F	F	F	F	F	F
FEBFCST		A	F	F	F	F	F	F	F	F	F	F	F
MARFCST		A	A	F	F	F	F	F	F	F	F	F	F
APRFCST		A	A	A	F	F	F	F	F	F	F	F	F
MAYFCST		A	A	A	A	F	F	F	F	F	F	F	F
JUNFCST		A	A	A	A	A	F	F	F	F	F	F	F
JULFCST		A	A	A	A	A	A	F	F	F	F	F	F
AUGFCST		A	A	A	A	A	A	A	F	F	F	F	F
SEPFCST		A	A	A	A	A	A	A	A	F	F	F	F
OCTFCST		A	A	A	A	A	A	A	A	A	F	F	F
NOVFCST		A	A	A	A	A	A	A	A	A	A	F	F
DECFCST		A	A	A	A	A	A	A	A	A	A	A	F

I tried the script below but I receive an error during logic validation that only "=" is supported in " *IS >= CATEGORY.FIRSTPLNMTH"

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%
*XDIM_MEMBERSET TIME=%YEAR%.JAN,%YEAR%.FEB,%YEAR%.MAR,%YEAR%.APR,%YEAR%.MAY,%YEAR%.JUN,%YEAR%.JUL,%YEAR%.AUG,%YEAR%.SEP,%YEAR%.OCT,%YEAR%.NOV,%YEAR%.DEC


*WHEN TIME.MONTHNUM
   *IS >= CATEGORY.FIRSTPLNMTH
      *REC(EXPRESSION=[CATEGORY].[FORECAST],CATEGORY=%CATEGORY_SET%)
   *ELSE
      *REC(EXPRESSION=[CATEGORY].[ACTUAL],CATEGORY=%CATEGORY_SET%)
*ENDWHEN
*COMMIT

FIRSTPLANMTH is a property in the CATEGORY dimension that contains the MONTHNUM of its first forecast month in the matrix above. Example, JANFCST, FIRSTPLNMTH=1 and so on...

Any idea how to get this done?

Thanks,

Marvin

Former Member
Former Member replied

Hi,

The first thing is maintain the monthnum as 01, 02, etc. (instead of 1,2, etc). The idea is to have 2 digits for all the monthnum. Then try your logic (validate it and run).

If the above doesnt work, please try the below logic:

*SELECT(%CURR_YEAR%,"YEAR",TIME,ID=%TIME_SET%)
*SELECT(%FORECASTMNTH%, "ID", CATEGORY, "FCST='Y'")

*XDIM_MEMBERSET CATEGORY=%FORECASTMNTH%
*XDIM_MEMBERSET TIME=BAS(%CURR_YEAR%.TOTAL)
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%

*FOR %CURRMNTH% = %FORECASTMNTH%
   *WHEN TIME.MONTHNUM
   *IS >= %CURRMNTH%.FIRSTPLNMTH
      *REC(EXPRESSION=[CATEGORY].[FORECAST])
   *ELSE
      *REC(EXPRESSION=[CATEGORY].[ACTUAL])
   *ENDWHEN
*NEXT

*COMMIT

Please maintain a property FCST as Y for all the FCST categories.

I hope you got the idea behind the logic.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question