Previous month data should be aggregated in next month

[former_member590808]

Hi,

I have written a BPC logic script to aggregate previous month data in next month. I have a package to execute this BPC logic script. Eg. When I execute package for June, result should be May Data(which is April+May)+ June Data.

Data for which am doing the aggregation is saved in Dummy_test Key Figure.

AMT key figure has original month Data.

When am executing the below logic script for eg. for June, records which are originally present in June are getting summed with May data. So May +June is not adding a new entry of May Data(A1 E2 100) in June. It is rather showing a single record as below

June Data at AMT:

A1 E1 100

May Data at Dummy_test Key Figure:

A1 E2 100

A1 E1 100

Post executing script:

June Data at Dummy_test Keyfigure:

A1 E1 200

Expected Output:

June Data at Dummy_test Keyfigure:

A1 E1 200

A1 E2 100

I have to do a simple addition of previous month in next month. Please help !

*XDIM_MEMBERSET APL_TIMES = %APL_TIMES_SET%
*XDIM_MEMBERSET APL_CATEGORY = DUMMY
*XDIM_MEMBERSET APL_KEYFIGURE = AMT,Dummy_test
//*XDIM_MEMBERSET MEASURES = PERIODIC


*SELECT(%Y%,YEAR,APL_TIMES,ID = %APL_TIMES_SET%)
*SELECT(%1%,MONTHNUM,APL_TIMES,ID = %APL_TIMES_SET%)
*SELECT(%N%,MONTHNUM,APL_TIMES,ID = TMVL(+1,%APL_TIMES_SET%))


*WHEN APL_CATEGORY 
	*IS DUMMY
		*WHEN APL_TIMES.MONTHNUM
			*IS 1
			*REC(EXPRESSION = %VALUE%,APL_TIMES = %Y%.04,APL_KEYFIGURE = Dummy_test,APL_CATEGORY= DUMMY)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 2			
			*REC(EXPRESSION = (([APL_TIMES].[%Y%.04],[APL_CATEGORY].[DUMMY],[APL_KEYFIGURE].[Dummy_test])+([APL_TIMES].[%Y%.05],[APL_CATEGORY].[DUMMY],[APL_KEYFIGURE].[AMT])),APL_TIMES = %Y%.05,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 3
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.05],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.06],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.06,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 4			
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.06],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.07],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.07,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		*WHEN APL_TIMES.MONTHNUM
			*IS 5
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.07],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.08],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.08,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 6			
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.08],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.09],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.09,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		*WHEN APL_TIMES.MONTHNUM
			*IS 7
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.09],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.10],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.10,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		*WHEN APL_TIMES.MONTHNUM
			*IS 8
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.10],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.11],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.11,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)			
		*ENDWHEN
		*WHEN APL_TIMES.MONTHNUM
			*IS 9
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.11],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%Y%.12],[APL_KEYFIGURE].[AMT]),APL_TIMES = %Y%.12,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 10
			
			*REC(EXPRESSION = ([APL_TIMES].[%Y%.12],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%N%.01],[APL_KEYFIGURE].[AMT]),APL_TIMES = %N%.01,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		*WHEN APL_TIMES.MONTHNUM
			*IS 11
			*REC(EXPRESSION = ([APL_TIMES].[%N%.01],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%N%.02],[APL_KEYFIGURE].[AMT]),APL_TIMES = %N%.02,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
		
		*WHEN APL_TIMES.MONTHNUM
			*IS 12
			
			*REC(EXPRESSION = ([APL_TIMES].[%N%.02],[APL_KEYFIGURE].[Dummy_test]+[APL_TIMES].[%N%.03],[APL_KEYFIGURE].[AMT]),APL_TIMES = %N%.03,APL_CATEGORY = DUMMY,APL_KEYFIGURE = Dummy_test)
		*ENDWHEN
	*ENDWHEN

[former_member186338]

Sorry, but not enough info!

Model type: Periodic or YTD?

TIME dimension screenshot?

Data and required result sample?

...

Please read: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

[former_member590808]

Model type: YTD

Attached screenshots for TIME master data, April Data, May Data and output appearing in May. The records are for H01001 Account ID .

Expected Output: April NOPRODUCT records are not appearing.

[former_member186338]

Sorry, but nothing attached!

And required logic is not clear without sample screenshot!

[former_member186338]

Sorry, but I will never answer questions with "Insert File" instead of correct "Insert Image"

[former_member186338]

Sorry again, but do you really want an answer?

Why not to read my blog: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

Never use RSA1 screenshots, use Excel BPC reports to show existing data.

Use Excel screenshots to provide required results.

Show sample for all months of the year.

Explain required logic in words, not using incorrect scripts.

Show your TIME dimension screenshot (second time asking)

...

etc

[former_member186338 · Accepted solution]

Script to update the selected month and all month's after selected month:

*SELECT(%Y%,YEAR,TIME,ID=%TIME_SET%) //%TIME_SET% - single member!
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%)

*SELECT(%T01%,[ID],TIME,YEAR=%Y% AND TIMEID>=%TID% AND CALC=N)
*SELECT(%TID02%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=2)
*SELECT(%T02%,[ID],TIME,TIMEID>=%TID02% AND ID=%T01%)
*SELECT(%TID03%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=3)
*SELECT(%T03%,[ID],TIME,TIMEID>=%TID03% AND ID=%T01%)
*SELECT(%TID04%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=4)
*SELECT(%T04%,[ID],TIME,TIMEID>=%TID04% AND ID=%T01%)
*SELECT(%TID05%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=5)
*SELECT(%T05%,[ID],TIME,TIMEID>=%TID05% AND ID=%T01%)
*SELECT(%TID06%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=6)
*SELECT(%T06%,[ID],TIME,TIMEID>=%TID06% AND ID=%T01%)
*SELECT(%TID07%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=7)
*SELECT(%T07%,[ID],TIME,TIMEID>=%TID07% AND ID=%T01%)
*SELECT(%TID08%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=8)
*SELECT(%T08%,[ID],TIME,TIMEID>=%TID08% AND ID=%T01%)
*SELECT(%TID09%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=9)
*SELECT(%T09%,[ID],TIME,TIMEID>=%TID09% AND ID=%T01%)
*SELECT(%TID10%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=10)
*SELECT(%T10%,[ID],TIME,TIMEID>=%TID10% AND ID=%T01%)
*SELECT(%TID11%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=11)
*SELECT(%T11%,[ID],TIME,TIMEID>=%TID11% AND ID=%T01%)
*SELECT(%TID12%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=12)
*SELECT(%T12%,[ID],TIME,TIMEID>=%TID12% AND ID=%T01%)

*WHEN TIME.MONTHNUM
*IS 1
*FOR %M01%=%T01%
*REC(EXPRESSION=%VALUE%,TIME=%M01%,ACCOUNT=BS111)
*NEXT
*IS 2
*FOR %M02%=%T02%
*REC(EXPRESSION=%VALUE%,TIME=%M02%,ACCOUNT=BS111)
*NEXT
*IS 3
*FOR %M03%=%T03%
*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)
*NEXT
*IS 4
*FOR %M04%=%T04%
*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)
*NEXT
*IS 5
*FOR %M05%=%T05%
*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)
*NEXT
*IS 6
*FOR %M06%=%T06%
*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)
*NEXT
*IS 7
*FOR %M07%=%T07%
*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)
*NEXT
*IS 8
*FOR %M08%=%T08%
*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)
*NEXT
*IS 9
*FOR %M09%=%T09%
*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)
*NEXT
*IS 10
*FOR %M10%=%T10%
*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)
*NEXT
*IS 11
*FOR %M11%=%T11%
*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)
*NEXT
*IS 12
*FOR %M12%=%T12%
*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)
*NEXT
*ENDWHEN

[former_member590808 · Accepted solution]

So does it means when am making some value changes in 2019.04 in PRD then while clearing the data in BPC i need to clear for all the following months also ?

[former_member186338 · Accepted solution]

nehayadav0911

Sorry, but if you change some value in some month for PERIODIC then you have to calculate again al month's after for YTD!

[former_member590808 · Accepted solution]

you have to calculate again al month's after for YTD!

By this you mean delete the data for following months as well or just executing the package again directly which calls the script ?

[former_member186338 · Accepted solution]

nehayadav0911

For sure, it's better to delete data to be 100% safe! And you can delete it in the same script.

Scenario that will result in incorrect data:

1. You have some value in PERIODIC

2. You deleted this value

3. You performed lite optimize with zero elimination for the model.

After zero elimination zero record will disappear and script will not recalculate YTD values...

If you don't use lite optimize with zero elimination after data changes - then everything will be correct...