Creating latest estimate, problem with merging categories

[Former Member]

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 · Accepted solution]

Hi Experts,

I tried revising my script to the following and I don't encounter the error now. Please see below for my script:

*SELECT(%CURR_YEAR%,YEAR,TIME,ID=%TIME_SET%)
*SELECT(%PLANMTH%,FIRSTPLNMTH,CATEGORY,ID=%CATEGORY_SET%)

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%,FORECAST
*XDIM_MEMBERSET TIME=BAS(%CURR_YEAR%.TOTAL)
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%
*XDIM_MEMBERSET MEASURES=PERIODIC

*WHEN CATEGORY
*IS FORECAST
*WHEN TIME.MONTHNUM
*IS >= %PLANMTH%
*REC(FACTOR=1,CATEGORY=%CATEGORY_SET%)
*ENDWHEN
*ENDWHEN
*COMMIT



*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%,ACTUAL
*XDIM_MEMBERSET TIME=BAS(%CURR_YEAR%.TOTAL)
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%
*XDIM_MEMBERSET MEASURES=PERIODIC

*WHEN CATEGORY
*IS ACTUAL
*WHEN TIME.MONTHNUM
*IS < %PLANMTH%
*REC(FACTOR=1,CATEGORY=%CATEGORY_SET%)
*ENDWHEN
*ENDWHEN
*COMMIT

Now my problem is that, the FORECAST data somehow is not written in OCT, NOV and DEC, instead, ACTUAL data is written I'm pretty sure I maintain the right MONTHNUM for each months. For example, I run this logic for JULFCST, I'll get the following weird results:

		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
ACTUAL		A	A	A	A	A	A	A	A	A	A	A	A
FORECAST	F	F	F	F	F	F	F	F	F	F	F	F

JANFCST		F	F	F	F	F	F	F	F	F	F	F	F
FEBFCST		A	F	F	F	F	F	F	F	F	A	A	A
MARFCST		A	A	F	F	F	F	F	F	F	A	A	A
APRFCST		A	A	A	F	F	F	F	F	F	A	A	A
MAYFCST		A	A	A	A	F	F	F	F	F	A	A	A
JUNFCST		A	A	A	A	A	F	F	F	F	A	A	A
JULFCST		A	A	A	A	A	A	F	F	F	A	A	A
AUGFCST		A	A	A	A	A	A	A	F	F	A	A	A
SEPFCST		A	A	A	A	A	A	A	A	F	A	A	A
OCTFCST		A	F	F	F	F	F	F	F	F	F	F	F
NOVFCST		A	F	F	F	F	F	F	F	F	A	F	F
DECFCST		A	F	F	F	F	F	F	F	F	A	A	F

Any idea what's causing this?

Thanks,

Marvin

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Hi nilanjan,

Thanks for the reply, I tried changing the MONTHNUM to 2 digit, 01,02,03, etc. and used the logic above that you suggested(with just minor adjustments since I only need to run it on 1 forecast category at a time) please see below:

*SELECT(%CURR_YEAR%,"YEAR",TIME,ID=%TIME_SET%)
*SELECT(%FORECASTMNTH%, "FIRSTPLNMTH", CATEGORY, ID=%CATEGORY_SET%)
 
*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_MEMBERSET TIME=BAS(%CURR_YEAR%.TOTAL)
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%
 

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

The logic runs perfectly on most of the months except on some months which calculates nothing at all. I already process the dimension a couple of time but nothing seems to fix the problem. I also looked at the back end and I can see that their "FIRSTPLANMONTH" property values are maintained well, 01,02,03,04,05...etc

		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
ACTUAL		A	A	A	A	A	A	A	A	A	A	A	A
FORECAST	F	F	F	F	F	F	F	F	F	F	F	F
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												
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												
OCTFCST												
NOVFCST												
DECFCST		A	A	A	A	A	A	A	A	A	A	A	F

Hope you could help me regarding this. Additionally here's the log when I run it on the SCRIPT LOGIC TESTER on MAYFCST. Thought might help.

LGX:

*XDIM_MEMBERSET CATEGORY=MAYFCST
*XDIM_MEMBERSET TIME=BAS(2005.TOTAL)
*XDIM_MEMBERSET COSTCTR=2501200120
*WHEN TIME.MONTHNUM
*IS >= 05
*REC(EXPRESSION=[CATEGORY].[FORECAST])
*ELSE
*REC(EXPRESSION=[CATEGORY].[ACTUAL])
*ENDWHEN
*COMMIT

-

---

LOG:

LOG BEGIN TIME:2011-06-14 09:26:06

FILE:\ROOT\WEBFOLDERS\APPSET_NAME\ADMINAPP\COST\TEST.LGF

USER:DOMAIN\USERNAME

APPSET:APPSET_NAME

APPLICATION:COST

[INFO] GET_DIM_LIST(): I_APPL_ID="COST", #dimensions=9

ACCOUNT,ASSETTYPE,CATEGORY,COSTCTR,CURRENCY,DATASRC,LINEITEM,MEASURES,TIME

#dim_memberset=3

CATEGORY:MAYFCST,1 in total.

TIME:2005.JAN,2005.FEB,2005.MAR,2005.APR,2005.MAY,...12 in total.

COSTCTR:2501200120,1 in total.

REC :[CATEGORY].[FORECAST]

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 773.66 ms. 0 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 755.80 ms. 8 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :9.71 ms.

NO RECORDS GENERATED.

CALCULATION END.

#dim_memberset=3

CATEGORY:MAYFCST,1 in total.

TIME:2005.JAN,2005.FEB,2005.MAR,2005.APR,2005.MAY,...12 in total.

COSTCTR:2501200120,1 in total.

REC :[CATEGORY].[ACTUAL]

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 732.82 ms. 0 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 742.16 ms. 4 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :8.71 ms.

NO RECORDS GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 0 RECORDS ARE GENERATED.

SCRIPT RUNNING TIME IN TOTAL:3.36 s.

LOG END TIME:2011-06-14 09:26:10

Thanks,

Marvin

[Former Member · Accepted solution]

Hi,

I see the problem.

If you take a look at the code in the log:

*XDIM_MEMBERSET CATEGORY=MAYFCST
*XDIM_MEMBERSET TIME=BAS(2005.TOTAL)
*XDIM_MEMBERSET COSTCTR=2501200120
*WHEN TIME.MONTHNUM
*IS >= 05
   *REC(EXPRESSION=[CATEGORY].[FORECAST])
*ELSE
   *REC(EXPRESSION=[CATEGORY].[ACTUAL])
*ENDWHEN
*COMMIT

The code has been scoped to work for the category MAYFCST. This means that the code will work only if there is already some data in this category. If there is none, then the WHEN statement is not executed at all.

You might have had record for the other FCST categories, thats the reason it worked for them. However, you dont have any prior record in the MAY,SEP,OCT, and NOV FCST categories and thats why its not working.

Usually, the script logic works on transaction data mode, which means that the script will work only when there is some transactional record for the region maintained in the XDIM statements. However, it can be changed to masterdata mode, in which the code will work even if there is no transactional record for the data region.

Please take a look at the SAP note # 1474115.

There is a zip file named EXAMPLE.ZIP in the above note. This file has an example as well.

Hope this helps.

[Former Member · Accepted solution]

Hi nilanjan,

Thank you very much again. Yes, I understand your explanation above that's why I'm wondering why it's not working on MAY, SEP, OCT, and NOV forecast since I don't have any data on the other forecast months as well. I only have data on the ACTUAL and FORECAST(running forecast) category which is supposed to merge in those forecast categories as mapped above. Anyway I just revised my logic into my original logic and it was working well now. I think the only problem I have on my original logic is that the MONTHNUM are not maintained as 01,02,03,04 etc. as you suggested. Thank you very much again. Here's the revised script.

*SELECT(%CURR_YEAR%,YEAR,TIME,ID=%TIME_SET%)
*SELECT(%PLANMTH%,FIRSTPLNMTH,CATEGORY,ID=%CATEGORY_SET%)

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%,FORECAST,ACTUAL
*XDIM_MEMBERSET TIME=BAS(%CURR_YEAR%.TOTAL)
*XDIM_MEMBERSET COSTCTR=%COSTCTR_SET%

*WHEN CATEGORY
*IS FORECAST
*WHEN TIME.MONTHNUM
*IS >= %PLANMTH%
*REC(FACTOR=1,CATEGORY=%CATEGORY_SET%)
*ENDWHEN
*ENDWHEN


*WHEN CATEGORY
*IS ACTUAL
*WHEN TIME.MONTHNUM
*IS < %PLANMTH%
*REC(FACTOR=1,CATEGORY=%CATEGORY_SET%)
*ENDWHEN
*ENDWHEN
*COMMIT

Cheers,

Marvin