cancel
Showing results for 
Search instead for 
Did you mean: 

YTD to periodic data calculation in different model

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

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
0 Kudos

Sir,

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

Please help.

former_member186338
Active Contributor
0 Kudos

I have already provided you a correct script.

Former Member
0 Kudos

Sir,

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

Please help sir.

former_member186338
Active Contributor
0 Kudos

Please provide a screenshot of your time dimension administration.

Former Member
0 Kudos

Sir,

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

former_member186338
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Vadim,

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

vadim-error.jpg

former_member186338
Active Contributor
0 Kudos

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
0 Kudos

Hi Vadim,

Please check, if this help in reading the script.