Skip to Content
avatar image
Former Member

YTD to periodic data calculation in different model

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

example-scrren.jpg (89.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Aug 24, 2017 at 11:26 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 24, 2017 at 12:17 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

    • 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!