PERIODIC to QTD conversion using Logic Script

[Former Member]

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 Member · Accepted solution]

It worked, thanks a lot. 🙂

[former_member186338]

OK, please read my blog: https://blogs.sap.com/2018/11/02/periodic-ytd-conversion-using-script-logic-in-bpc-nw/

My code can be adjusted to create QTD result:

For example, for the whole year the code will be:

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE
*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)
*XDIM_MEMBERSET MEASURES=PERIODIC
*SELECT(%Y%,[YEAR],TIME,[ID]=2007.TOTAL) //%Y% - will contain 2007

*WHEN TIME.MONTHNUM
*IS 1
*REC(EXPRESSION=%VALUE%,TIME=%Y%.01,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.02,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=BS111)
*IS 2
*REC(EXPRESSION=%VALUE%,TIME=%Y%.02,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=BS111)
*IS 3
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=BS111)
*IS 4
*REC(EXPRESSION=%VALUE%,TIME=%Y%.04,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=BS111)
*IS 5
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=BS111)
*IS 6
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=BS111)
*IS 7
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=BS111)
*IS 8
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=BS111)
*IS 9
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=BS111)
*IS 10
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=BS111)
*IS 11
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=BS111)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=BS111)
*IS 12
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=BS111)
*ENDWHEN

[Former Member]

I did read that blog, the code that you have provided will work irrespective of the time period passed by the user. My requirement is that when a user pass Time = 2018.MAY, then QTD should be based for 2018.MAY only not for the whole year.

[former_member186338]

P.S. About user selection of months:

There are restrictions for QTD conversion:

User can select number of months starting from first month of quarter up to some month!

Ex:

2019.JAN,2019.FEB - Correct

2019.JAN,2019.FEB,2019.MAR,2019.APR - Correct

2019.APR,2019.MAY - Correct

2019.MAY,2019.JUN - Incorrect

Etc..

[former_member186338]

2018.MAY - incorrect user selection, assuming user select source month

If it's the last month withing quarter then source is: first month of quarter... user selected month: 2018.APR,2018.MAY

[Former Member]

user can select any Month, for which calculations are required. If user has selected 2018.MAY then it will take logic script should take 2018.APR & 2018.MAY for retrieving data and should write data at 2018.MAY.

[Former Member]

P.S. - only 1 Time period is allowed to select.

[former_member186338]

Let's assume that the user will select only SINGLE month and for this selected month QTD value will be calculated. Is it acceptable?

Because in case of multiple month selection the calculations will be incorrect sometimes!

P.S. It's critical to define proper logic for user selection

[former_member186338]

OK, I will present the code!

[former_member186338]

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

You have to explain the required logic using sample data and logic description! It's very hard to understand the logic based on some strange script...

[Former Member]

sample data added :-

DEPSQTD = NETINCOME / DSCQAVG

NETINCOME is defined in the script as :- BAS(CONTINUINGOPSINC), BAS(NCI_CONTOPS)

DSCQAVG (OWNACCOUNT) :- Input by user

*XDIM_MEMBERSET BPCGACCOUNT = BAS(CONTINUINGOPSINC), BAS(NCI_CONTOPS)  //NETINCOME
*XDIM_MEMBERSET CATEGORY = ACTUAL 	// FIXED 
*XDIM_MEMBERSET ENTITY=%ENTITY_SET% 	//USER INPUT
*XDIM_MEMBERSET CONSOLGROUP= MG_M_TOP	// FIXED 
*XDIM_MEMBERSET COSTCENTERAREA = <ALL>
*XDIM_MEMBERSET CURRENCY = USD		//FIXED
*XDIM_MEMBERSET DATASRC =  BAS(ALL_DATASRCS)	
*XDIM_MEMBERSET FLOW = F_CLO		//FIXED
*XDIM_MEMBERSET INTERCO = <ALL>
*LOOKUP Ownership
*DIM ENTITY= E.NA		//FIXED
*DIM INTERCO = I.NA		// FIXED
*DIM CONSOLGROUP = MG_M_TOP	//FIXED
*DIM CATEGORY = ACTUAL		//FIXED
*DIM MEASURES= YTD			
*DIM Q02:OWNACCOUNT = DSCQAVG	//(LOOKUP FOR OWNACCOUNT - DSCQAVG)
*ENDLOOKUP		
*SELECT(%YR%,[YEAR],TIME,[ID]=%TIME_SET%)		//YEAR SELECTION BASED ON INPUT
*XDIM_MEMBERSET TIME AS %02%=%YR%.JAN,%YR%.FEB		//VARIABLE FOR JAN & FEB
*XDIM_MEMBERSET TIME AS %03%=%YR%.JAN,%YR%.FEB,%YR%.MAR	//VARIABLE FOR JAN,FEB & MAR
*XDIM_MEMBERSET TIME = %02%		
*WHEN TIME
*IS *
*REC(FACTOR=-1/LOOKUP(Q02),TIME=%YR%.FEB ,BPCGACCOUNT=DEPSQTD)	//CALC FOR NETINCOME - JAN & FEB, Whereas FOR DSCQAVG TIME = FEB and Data will be saved at FEB Time period. 
*ENDWHEN
*XDIM_MEMBERSET TIME = %03%
*WHEN TIME
*IS *
*REC(FACTOR=-1/LOOKUP(Q02),TIME=%YR%.MAR ,BPCGACCOUNT=DEPSQTD)	//CALC FOR JAN, FEB & MAR ; Whereas FOR DSCQAVG TIME = MAR and Data will be saved at Mar Time period.
*ENDWHEN

[former_member186338]

Sorry, but still not clear!

First of all, forget about division by DSCQAVG for a moment - will be added later!

Please, explain, what do you mean by QTD conversion in this particular case. Provide sample calculation for all months of the year (without division)!

Not you script code!!!!!

[Former Member]

Sorry, QTD is the Measures and Consolidations model is Periodic

Below is the sample calculations for all months:-