PERIODIC to QTD conversion using Logic Script

[former_member542409]

Hi Experts,

I'm facing issue when i try to retrieve the QTD calculations .Issue is that Time period will be entered by the user and based on that, calculations will be performed such as Consolidations model --> DEPSQTD = NETINCOME / DSCQAVG(Ownership Model) . Lets' say if i take 2018.FEB , then it should perform, DEPSQTD = NETINCOME (SUM - TIME 2018.JAN & 2018.FEB) / DSCQAVG (TIME 2018.FEB).

NETINCOME is a member formula = BAS(CONTINUINGOPSINC) - BAS(NCI_CONTOPS)
The code that i am using calculates correctly but it does perform the calculations for other time period as well.

BPC 810SP08 , BW 740SP13
Below is the script i used to perform the testing of Time FEB & MAR :-.

*XDIM_MEMBERSET BPCGACCOUNT = BAS(CONTINUINGOPSINC), BAS(NCI_CONTOPS)
*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*XDIM_MEMBERSET CONSOLGROUP= MG_M_TOP
*XDIM_MEMBERSET COSTCENTERAREA = <ALL>
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET DATASRC =  BAS(ALL_DATASRCS)
*XDIM_MEMBERSET FLOW = F_CLO
*XDIM_MEMBERSET INTERCO = <ALL>
*LOOKUP Ownership
*DIM ENTITY= E.NA
*DIM INTERCO = I.NA
*DIM CONSOLGROUP = MG_M_TOP
*DIM CATEGORY = ACTUAL
*DIM MEASURES= YTD
*DIM Q02:OWNACCOUNT = DSCQAVG
*ENDLOOKUP
*SELECT(%YR%,[YEAR],TIME,[ID]=%TIME_SET%)
*XDIM_MEMBERSET TIME AS %02%=%YR%.JAN,%YR%.FEB
*XDIM_MEMBERSET TIME AS %03%=%YR%.JAN,%YR%.FEB,%YR%.MAR
*XDIM_MEMBERSET TIME = %02%
*WHEN TIME
*IS *
*REC(FACTOR=-1/LOOKUP(Q02),TIME=%YR%.FEB ,BPCGACCOUNT=DEPSQTD)
*ENDWHEN
*XDIM_MEMBERSET TIME = %03%
*WHEN TIME
*IS *
*REC(FACTOR=-1/LOOKUP(Q02),TIME=%YR%.MAR ,BPCGACCOUNT=DEPSQTD)
*ENDWHEN


UJKT SCRIPT LOG:-   ENTITY = BAS(M_TOP),TIME =2018.FEB

LOG BEGIN TIME:2019-03-08 07:34:07

FILE:\ROOT\WEBFOLDERS\AES \ADMINAPP\Consolidations\TEST.LGF
USER:BPCACC01
APPSET:US_GAAP
APPLICATION:Consolidations
[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidations", #dimensions=11 
BPCGACCOUNT,CATEGORY,CONSOLGROUP,COSTCENTERAREA,CURRENCY,DATASRC,ENTITY,FLOW,INTERCO,MEASURES,TIME

#dim_memberset=8 
BPCGACCOUNT:A400105,A400110,A400115,A400120,A400125,...237 in total.
CATEGORY:ACTUAL,1 in total.
ENTITY:01DO,01GB,01KZ,01PH,01US,...2322 in total.
CONSOLGROUP:MG_M_AESMGT,1 in total.
CURRENCY:USD,1 in total.
DATASRC:CF_ADJ,CF_ADJ_SYS,CF_BALANCE,CF_BAL_SYS,CF_DISCOPS,...45 in total.
FLOW:F_CLO,1 in total.
TIME:2018.FEB,2018.JAN,2 in total.
REC :%value%*(-1/LOOKUP(Q02))

CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 4.00 ms. 29223  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY LOOKUP DATA FROM APPLICATION: Ownership
QUERY TIME : 2.00 ms. 2  RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :5.00 ms.
5772  RECORDS ARE GENERATED.
CALCULATION END.

[former_member186338 · Accepted solution]

Universal code will be:

*LOOKUP Ownership
*DIM ENTITY= E.NA
*DIM INTERCO = I.NA
*DIM CONSOLGROUP = MG_M_TOP
*DIM CATEGORY = ACTUAL
*DIM MEASURES= YTD
*DIM Q02:OWNACCOUNT = DSCQAVG
*ENDLOOKUP

*SELECT(%PER%,PERIOD,TIME,ID=%TIME_SET%) //%TIME_SET% - Single value: 2018.FEB
*SELECT(%Y%,YEAR,TIME,ID=%TIME_SET%)

*SELECT(%L_JAN%,ID,TIME,PERIOD=JAN AND YEAR=%Y%)
*SELECT(%L_FEB%,ID,TIME,PERIOD=JAN,FEB AND YEAR=%Y%)
*SELECT(%L_MAR%,ID,TIME,PERIOD=JAN,FEB,MAR AND YEAR=%Y%)
*SELECT(%L_APR%,ID,TIME,PERIOD=APR AND YEAR=%Y%)
*SELECT(%L_MAY%,ID,TIME,PERIOD=APR,MAY AND YEAR=%Y%)
*SELECT(%L_JUN%,ID,TIME,PERIOD=APR,MAY,JUN AND YEAR=%Y%)
*SELECT(%L_JUL%,ID,TIME,PERIOD=JUL AND YEAR=%Y%)
*SELECT(%L_AUG%,ID,TIME,PERIOD=JUL,AUG AND YEAR=%Y%)
*SELECT(%L_SEP%,ID,TIME,PERIOD=JUL,AUG,SEP AND YEAR=%Y%)
*SELECT(%L_OCT%,ID,TIME,PERIOD=OCT AND YEAR=%Y%)
*SELECT(%L_NOV%,ID,TIME,PERIOD=OCT,NOV AND YEAR=%Y%)
*SELECT(%L_DEC%,ID,TIME,PERIOD=OCT,NOV,DEC AND YEAR=%Y%)

*SELECT(%SM%,ID,TIME,ID=%L_%PER%%) //%SM% will contain correct set of months: 2018.JAN,2018.FEB

*XDIM_MEMBERSET TIME=%SM%

*XDIM_MEMBERSET BPCGACCOUNT = BAS(CONTINUINGOPSINC), BAS(NCI_CONTOPS)
*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*XDIM_MEMBERSET CONSOLGROUP= MG_M_TOP
*XDIM_MEMBERSET COSTCENTERAREA = <ALL>
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET DATASRC =  BAS(ALL_DATASRCS)
*XDIM_MEMBERSET FLOW = F_CLO
*XDIM_MEMBERSET INTERCO = <ALL>

*WHEN TIME
*IS *
*REC(FACTOR=-1/LOOKUP(Q02),TIME=%TIME_SET% ,BPCGACCOUNT=DEPSQTD)
*ENDWHEN

The trick is here: *SELECT(%SM%,ID,TIME,ID=%L_%PER%%)

[former_member186338 · Accepted solution]

P.S. In order to ensure that only single period is selected by user, please read my blog:

https://blogs.sap.com/2018/11/04/how-to-check-that-only-single-member-is-passed-from-dm-prompt-in-bp...

[former_member542409 · Accepted solution]

It worked, thanks a lot. 🙂