Creating latest estimate, problem with merging categories
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?
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.