Skip to Content
0
Former Member
Jun 12, 2011 at 03:26 AM

Creating latest estimate, problem with merging categories

112 Views

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