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?
Nilanjan Chatterjee replied
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.