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...

[former_member186338]

"have read this blog. Very helpful but it did not work for my Time elements I have 2018.04 as 1 and then 12 is 2019.03"

Sorry, but what is the issue???

Just correct my script according to your fiscal year!

For example, first script:

//Lets assume that variable $FE$ contain user selected fiscal year - 2018
//2018.04,...,2018.12,2019.01,...,2019.03
//$FE$ will be equal to the first year in the list and we have to calculate second year

//Calculate %NEXTY% based of the last month of the fiscal year - MAR
*SELECT(%NEXTY%,[YEAR],TIME,[YEAR]=$FE$ AND [MONTHNUM]=12 AND [CALC]=N)
//%NEXTY%=2019

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE
*XDIM_MEMBERSET MEASURES=PERIODIC
*XDIM_MEMBERSET TIME=BAS($FE$.TOTAL)

*WHEN TIME.MONTHNUM
*IS 1
*REC(EXPRESSION=%VALUE%,TIME=$FE$.04,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.05,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.06,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.07,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.08,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.09,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.10,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.11,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=$FE$.12,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%NEXTY%.01,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%NEXTY%.02,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%NEXTY%.03,ACCOUNT=BS111)
*IS 2
...

[former_member186338]

Ups, sorry! Made a mistake in next year calculation!

[former_member186338]

Instead of:

//Calculate %NEXTY% based of the last month of the fiscal year - MAR
*SELECT(%NEXTY%,[YEAR],TIME,[YEAR]=$FE$ AND [MONTHNUM]=12 AND [CALC]=N)
//%NEXTY%=2019

Use:

*SELECT(%TIDMAR%,[TIMEID],TIME,[YEAR]=$FE$ AND [MONTHNUM]=12 AND [CALC]=N) //%TIDMAR%=20190300
*SELECT(%TIDG%,[TIMEID],TIME,[TIMEID]>%TIDMAR% AND [CALC]=N) //%TIDG%=20190400,20190500...
*SELECT(%NEXTY%,[YEAR],TIME,[TIMEID]>%TIDMAR% AND [TIMEID]<=%TIDG% AND [CALC]=N) //%NEXTY%=2019 from 2019.04

Will properly calculate %NEXTY%

[former_member590808]

What will be the user input then ? For the previous script i was passing 2018.04/2018.05 in input.

[former_member186338]

nehayadav0911

User input depends on business requirements!

If you need to calculate all months of fiscal tear - then combobox with list of years is a good solution. The selected year will be passed in variable $FE$

[former_member590808]

with the script mentioned all the data values get fetched freshly everytime. If I have to delete the data for a particular month eg. June and then run above script again , it will fetch fresh values from April till June right ? I want the data to fetch only fresh value for June. My april and may data should remain untouched.

[former_member186338]

nehayadav0911

Yes, my script will recalculate data for the whole fiscal year.

Another correct solution is:

1. Allow only single month selection!

2. Recalculate all month's after and including selected month.

Is it what you want?

[former_member590808]

Yes that is what I want, only single month to get affected.

vadim.kalinin

[former_member186338]

nehayadav0911

"only single month to get affected" - sorry, but this is incorrect! Not single month but also all months after selected month! Big difference!

[former_member590808]

Okay , so if for eg. I delete the data of june and now I changed some data in PRD which will get fetched here in YTD format.

So what changes i need to make to the code so that only june and months post that are affected and not april and may

[former_member186338]

After providing additional info I can see that you are performing simple PERIODIC to YTD conversion! Having fiscal year from April to March.

"I can avoid Adding sum of all past months but simply pick the data of last month at Dummy_test." - wrong idea!

Please read my blog:

https://blogs.sap.com/2018/11/02/periodic-ytd-conversion-using-script-logic-in-bpc-nw/

[former_member590808]

I have read this blog. Very helpful but it did not work for my Time elements

I have 2018.04 as 1 and then 12 is 2019.03 .. it was giving some mismatch for 2019.01-2019.03. therefore, I wrote this script

[former_member590808]

Hi,

My requirement is 2018.05 = 2018.04+2018.05.

2018.06= 2018.05+2018.06+2018.04 and so on..

I am saving the aggregated data that is 2018.04+2018.05 in Dummy_test Key Figure. So,  the formula becomes ([APL_TIMES].[%Y%.04],[APL_CATEGORY].[DUMMY],[APL_KEYFIGURE].[Dummy_test])+([APL_TIMES].[%Y%.05],[APL_CATEGORY].[DUMMY],[APL_KEYFIGURE].[AMT])

I am using a different Key figure because Dummy_test will have aggregated data and AMT will have base value and hence I can avoid Adding sum of all past months but simply pick the data of last month at Dummy_test.

But the problem now is, when am using the above formula for records present in 2018.04, where data is not present in 2018.05, they are not getting aggregated. Eg. 2018.04+2018.05 for the first row of NOPRODUCT/H01001/1000_SRVS/2018.04 did not happen because the corresponding record entry is not present in 2018.05(AMT).

Below data at AD01/1000_PRJS is happening properly because there was some data present at 2018.05 on that combination at AMT(Measures: YTD)

Master Data of Time Dimension:

Hope the problem is clear now. Sorry for the confusion, Vadim.

Thank you !