YTD to periodic data calculation in different model

[Former Member]

Hi Friends,

We are on BPC 10.0 NW

Trying to move GL balances(YTD) in legal consolidation application to other application (financial type- periodic).

Script written-

*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'") 

*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'") 

*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%") 

//*XDIM_MEMBERSET TIME = %TM1% 

*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'") 

*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) 

*XDIM_MEMBERSET ACCOUNT = %ACC% 

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET% 

//*WHEN_REF_DATA = MASTER_DATA 

*DESTINATION_APP = CONSOL_PER 

*WHEN TIME 

*IS %TM1% 

*WHEN TIME.MONTHNUM 

*IS "12" 

*ELSE 

*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-1,%TM1%)],TIME = %TM1%) 

*ENDWHEN 

*ENDWHEN

Now the issue that we are facing is doing the subtraction when the flow between two periods are different.

E.g, Our Financial Year ends on 31st March so we have 2017.12 closing period & then 2018.01 as opening.

AuditTrail Flow Time Amount

D_UPLOAD 170 2017.12 1000

D_UPLOAD 120 2017.12 200

D_UPLOAD_OP 100 2017.12 2000

D_UPLOAD_OP 100 2018.01 3200

D_UPLOAD 120 2018.01 300

period 2017.12 is having data in flow 100, 120 & 170

but flow 2018.01 have data only in 100 & 120.

Result of this script is

D_UPLOAD_OP 100 2018.01 1200 (i.e, 3200-2000)

D_UPLOAD 120 2018.01 100 (i.e, 300-200)

The data of Flow in 170 is not getting captured in the subtraction.

Desired output should be-

D_UPLOAD_OP 100 2018.01 1200 (i.e, 3200-2000)

D_UPLOAD 120 2018.01 100 (i.e, 300-200)

D_UPLOAD 170 2018.01 -1000 (i.e, 0 - 1000)

Please help in resolving the issue.

PS- Clearly visible example is attached with this thread.

example-scrren.jpg

Regards,

Nukul

[former_member186338]

OK, now the script is readable!

But you did not provided extra info mentioned in my blog! I have no time to ask each question individually!

In general: this line is wrong

*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-1,%TM1%)],TIME = %TM1%)

The script has to be:

*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'") 
*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'") 
*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%") 
*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'") 

*XDIM_MEMBERSET ACCOUNT = %ACC% 
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET% 
*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08

*DESTINATION_APP = CONSOL_PER 
*WHEN TIME 
*IS %TM1% //2017.09
*REC(EXPRESSION = %VALUE%,TIME = %TM1%) // add value
*IS TMVL(-1,%TM1%) //2017.08 
*WHEN TIME.MONTHNUM 
*IS<>12 //If DEC - skip; DEC will be if %TM1% is JAN
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value
*ENDWHEN 
*ENDWHEN

[Former Member]

Sir,

updated the question with example in table and the desired output from the script.

Please help.

[former_member186338]

I have already provided you a correct script.

[Former Member]

Sir,

Unfortunately this script is also giving the same result, data in Flow 170 getting ignored with this also.

Please help sir.

[former_member186338]

Please provide a screenshot of your time dimension administration.

[Former Member]

Sir,

Please find attached the screenshot of Time Dimension.time-dim-screen.jpg

[former_member186338]

Incorrect TIME dimension setup!

MONTH_NUM=1 has to be JAN etc!

Please explain what calendar months are corresponding to ID's

2018.01 is calendar 2018.04?

Also in your original post you are talking about data from 2017 to be subtracted from data of the first month of 2017. But it's not YTD!

[Former Member]

Sir,

Ours is April to March Financial year, so 2018.01 is April not JAN.

We want the subtraction of data in the way i attached in example-screen.jpg.

Attached again the same for your ready reference.

Each period is storing YTD data as shown in the example, so for deriving periodic values i want to just do the subtraction of current period(2018.01) less previous period 2017.12.

The same exercise needs to be done next month when current period is 2018.02 & previous period is 2018.01.

example-scrren.jpg

[former_member186338]

Sorry, PROPERTY values in TIME dimension require correct values!

And it's mandatory to have PERIOD property of the first month of the year to be JAN, not APR! Same for other periods.

If you need real calendar month - create another property like CALPERIOD and fill it like you want.

"Each period is storing YTD data as shown in the example, so for deriving periodic values i want to just do the subtraction of current period(2018.01) less previous period 2017.12." - you are talking not about YTD calculation. YTD figures are accumulated starting from the first month of the year, not considering previous year.

To implement the logic you want look on the following code:

*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'") 
*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'") 
*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%") 
*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'") 
*XDIM_MEMBERSET ACCOUNT = %ACC% 
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET% 
*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08
*DESTINATION_APP = CONSOL_PER 
*WHEN TIME 
*IS %TM1% //2017.09
*REC(EXPRESSION = %VALUE%) // add value
*IS TMVL(-1,%TM1%) //2017.08 
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value
*ENDWHEN

This code will always subtract the previous month from the current month.

I am assuming that you are running this code as a DM package.

[Former Member]

Sir,

I am running the script through DM, and it is doing the subtraction between two time periods.

But issue came up when I have data in a Flow type(say 170 in example screen) in previous period 2017.12 but said flow doesn't have data in current period 2018.01. Then this subtraction result gets failed.

Overall conclusion is that the output of this script is based on current period flows only.

Please see the example screen attached.

Need your help in achieving correct subtraction to derive periodic values.

[former_member186338]

Sorry, but there is no scope for FLOW dimension in my script. And my script has scope for both periods:

*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08

and for previous period it will always subtract value for ANY flow:

*IS TMVL(-1,%TM1%) //2017.08 
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value

May be you are testing not my script?

[Former Member]

Sir,

I am testing with your script only, and i am not filtering any Flow type still it is behaving like this.

I am worried if it is the * REC command property that it will only do addition/subtraction within common dimensions only.

Since I tried the script with addition command also but still its picking only the common flow types data only.

Please help if this can be the case.

[former_member186338]

Please test my script in UJKT and provide resulting lgx and log.

"I am worried if it is the * REC command property that it will only do addition/subtraction within common dimensions only." - absolutely incorrect!

[former_member186338]

Please correct your post to have script in a readable form, then I will answer/

[Former Member]

Hi Vadim,

I have attached the script in Word, please have a look and help in resolving the issue.

vadim-error.jpg

[former_member186338]

I do not need a picture of your script!

Please correct original post to have proper lines of script.

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

and provide other required info.

[Former Member]

Hi Vadim,

Please check, if this help in reading the script.