TMVL for changed Time period in script logic

[Former Member]

Dear All

we have a scenario, where logic is placed in default logic.

However till now the time period is hardcoded with parent member and its taking toomuch time.

I got idea, so that to take only changed time members from , as this logic is placed in default logic.

but the hurdle, i want only months from input month to end of the year.

For example if user inputs value in 2014.03 and 2015.05, my scope of time should be following

2014.03,2014.04... 2014.12

and 2015.05 , 2015.06... 2015.12.

I tried the below piece of code, when I ran in uJKT, it does well,

*FOR %TMS%=0,1,2,3,4,5,6,7,8,9,10,11

*XDIM_ADDMEMBERSET TIME=TMVL(%TMS%,%TIME_SET%)

*NEXT

but when i placed in default logic, and i try to input values in 2015.03 and 2015.05

i.e multiple months, the initial value is not going to script logic and unable to generate the other months.

could you please advise how can i approach

[former_member186338 · Accepted solution]

Hi Vijay,

First of all, can you explain the logic you want to implement? Please read:

Second - please read about default.lgf:

And at the end - read help for TMVL: TMVL Parameter - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

For multiple TIME members in TMVL

"The first period of the TIME_SET is used as the base period for a negative offset and the last period of the TIME_SET is used as the base period for a positive offset."

Vadim

[Former Member · Accepted solution]

dear Vadim

thanks for the reply.

We are trying to calculate Weighted averages using BADI. So far the badi was hardcoded with Time as bas (Total). Hence its fetching all bas members and loop executing for more number of times and timing out. We tried to restrict  account and entity by passing only changed values.

Also, user wants only changed Time period to pass in to script and then to BADI.

For example if user changes value in 2015.05 , data posted into Audit trail -> Manual

using this value, Audit trail members YTD and QTD should generate for 2015.05 , 2015.06 .. 2012.12.

My Idea is to fetch the values from %Time_set% and generate the consecutive months i.e 2015.06 to 2015.12. and if user inputs both 2014.02 and 2015.04,, i want to generate 2014.02 to 2014.12 and 2015.04 to 2015.12 and pass it to BADI.

I require consecutive months, because the weighted averages will user input value and calculate for next months. Weighted averages are just like YTD averages,but uses Month numbers which will be manged by BADI>

Here is the script logic which i refined on time , just test one scenario i prepared this.

When i input values for 2015.05 and 2015.06, none of the calculations happend for YTD,QTD.

However when i ran in UJKT, by passing data region area as 2015.01, it does well and i could see values generated in YTd and qtd.. So i was thinking that since i mad input on multiple members of same year its conflicting. could you please advise.

If getting consecutive months is not possible, i am still ok get the all months of the inputted month.

i.e if user inputs 2014.02, if i can scope entire 2014 months also fine, but performance still effects to BADI to timeout.

please advise.

//This logic is inserted into defalut logic

//Delete values from Reporting AudiTrail

*XDIM_MEMBERSET MEASURES=PERIODIC

*XDIM_MEMBERSET AUDITTRAIL=YTD, QTD

*XDIM_MEMBERSET REPORTING=BBL

*XDIM_MEMBERSET ENTITY = AF_015

*XDIM_MEMBERSET ACCOUNT = ACT_02

*XDIM_FILTER CATEGORY = DATA_INPUT

*FOR %N%=0,1,2,3,4,5,6,7,8,9,10,11

*XDIM_ADDMEMBERSET TIME=TMVL(%N%,%TIME_SET%)

*NEXT

*WHEN ACCOUNT

*IS *

*REC(FACTOR = 0)

*ENDWHEN

*XDIM_MEMBERSET MEASURES=PERIODIC

*XDIM_MEMBERSET AUDITTRAIL= MANUAL

*XDIM_MEMBERSET REPORTING=BBL

*XDIM_MEMBERSET ENTITY = AF_015

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET ACCOUNT = ACT_02

*XDIM_FILTER CATEGORY = DATA_INPUT

*FOR %N%=0,1,2,3,4,5,6,7,8,9,10,11

*XDIM_ADDMEMBERSET TIME=TMVL(%N%,%TIME_SET%)

*NEXT

//insert BADI

*START_BADI CALCULATIONS

//[{parameter_name}={value}]

//...

*END_BADI

-------------------*****************---------------------************

ujkt results , with data region 2015.01 passed and account ACT_02, entity AF_015 hardcoded to test

ideally, Account, entity would come from input template.

LGX:

*XDIM_MEMBERSET MEASURES=PERIODIC

*XDIM_MEMBERSET AUDITTRAIL=YTD, QTD

*XDIM_MEMBERSET REPORTING=BBL

*XDIM_MEMBERSET ENTITY = AF_015

*XDIM_MEMBERSET ACCOUNT = PR_02

*XDIM_FILTER CATEGORY = DATA_INPUT

*XDIM_ADDMEMBERSET TIME= 2015.01

*XDIM_ADDMEMBERSET TIME= 2015.02

*XDIM_ADDMEMBERSET TIME= 2015.03

*XDIM_ADDMEMBERSET TIME= 2015.04

*XDIM_ADDMEMBERSET TIME= 2015.05

*XDIM_ADDMEMBERSET TIME= 2015.06

*XDIM_ADDMEMBERSET TIME= 2015.07

*XDIM_ADDMEMBERSET TIME= 2015.08

*XDIM_ADDMEMBERSET TIME= 2015.09

*XDIM_ADDMEMBERSET TIME= 2015.10

*XDIM_ADDMEMBERSET TIME= 2015.11

*XDIM_ADDMEMBERSET TIME= 2015.12

*WHEN ACCOUNT

*IS *

*REC(FACTOR = 0)

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET MEASURES=PERIODIC

*XDIM_MEMBERSET AUDITTRAIL= MANUAL

*XDIM_MEMBERSET REPORTING=BBL

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET ENTITY = AF_015

*XDIM_MEMBERSET ACCOUNT = PR_02

*XDIM_FILTER CATEGORY = DATA_INPUT

*XDIM_ADDMEMBERSET TIME= 2015.01

*XDIM_ADDMEMBERSET TIME= 2015.02

*XDIM_ADDMEMBERSET TIME= 2015.03

*XDIM_ADDMEMBERSET TIME= 2015.04

*XDIM_ADDMEMBERSET TIME= 2015.05

*XDIM_ADDMEMBERSET TIME= 2015.06

*XDIM_ADDMEMBERSET TIME= 2015.07

*XDIM_ADDMEMBERSET TIME= 2015.08

*XDIM_ADDMEMBERSET TIME= 2015.09

*XDIM_ADDMEMBERSET TIME= 2015.10

*XDIM_ADDMEMBERSET TIME= 2015.11

*XDIM_ADDMEMBERSET TIME= 2015.12

*START_BADI CALCULATIONS

*END_BADI

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-12-29 03:54:59

FILE:\ROOT\WEBFOLDERS\ZODW \ADMINAPP\METRICS\TEST.LGF

USER:NPONNAGANTI

APPSET:ZODW

APPLICATION:METRICS

[INFO] GET_DIM_LIST(): I_APPL_ID="METRICS", #dimensions=7

AUDITTRAIL,CATEGORY,MEASURES,ACCOUNT,ENTITY,TIME,REPORTING

#dim_memberset=7

MEASURES:PERIODIC,1 in total.

AUDITTRAIL:QTD,YTD,2 in total.

REPORTING:BBL,1 in total.

ENTITY:AF_015,1 in total.

ACCOUNT:ACT_02,1 in total.

CATEGORY:DATA_INPUT,1 in total.

TIME:2015.01,2015.02,2015.03,2015.04,2015.05,...12 in total.

REC :%value%*(0)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 24  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

24  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 24  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

AUDITTRAIL    CATEGORY    ACCOUNT    ENTITY    TIME    REPORTING    SIGNEDDATA

QTD    DATA_INPUT    ACT_02    AF_015    2015.01    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.02    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.03    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.04    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.05    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.06    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.07    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.08    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.09    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.10    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.11    BBL    0.00

QTD    DATA_INPUT    ACT_02    AF_015    2015.12    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.01    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.02    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.03    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.04    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.05    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.06    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.07    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.08    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.09    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.10    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.11    BBL    0.00

YTD    DATA_INPUT    ACT_02    AF_015    2015.12    BBL    0.00

24  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

Data Region:

MEASURES:PERIODIC

AUDITTRAIL:MANUAL

REPORTING:BBL

ENTITY:AF_015

ACCOUNT:ACT_02

CATEGORY:DATA_INPUT

TIME:2015.01,2015.02,2015.03,2015.04,2015.05,2015.06,2015.07,2015.08,2015.09,2015.10,2015.11,2015.12

QUERY TIME : 0.00 ms. 12  RECORDS QUERIED OUT.

QUERY TIME :0.00 S.

EXECUTION BADI:CALCULATIONS

QUERY: ON

WRITE: ON

BADI EXECUTION TIME IN TOTAL :1.00 ms.

DATA TO WRITE BACK:

AUDITTRAIL    CATEGORY    ACCOUNT    ENTITY    TIME    REPORTING    SIGNEDDATA

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.01    BBL    20.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.02    BBL    22.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.03    BBL    24.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.04    BBL    26.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.05    BBL    28.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.06    BBL    30.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.07    BBL    32.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.08    BBL    34.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.09    BBL    36.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.10    BBL    38.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.11    BBL    40.00

MANUAL    DATA_INPUT    ACT_02    AF_015    2015.12    BBL    42.00

12  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:4.00 s.

LOG END TIME:2015-12-29 03:55:03

[former_member186338 · Accepted solution]

Sorry, but can you explain in words and using some Excel table screenshot what do you mean by "We are trying to calculate Weighted averages using BADI."?

I am not sure that the idea in general will be successful...

By the way "AUDITTRAIL:QTD,YTD" - is a bad idea, don't use dimension members same as standard measures!

Vadim

[former_member186338 · Accepted solution]

P.S. And please, read again

Default.lgf is processed differently then DM package...

[Former Member · Accepted solution]

hi Vadim

sure I will explain Weighted averages.

input value say 2015.01 is 10

2015.02 is 20

normal YTD -> 2015.01 is 10, 2015.02 is 30

Weighted Average is like 2015.01 -> 10 *31  / 31 days = 10

2015. 02  -> 10 *31 + 28 * 20  / 59 days = 14.74

this is done by BADI, Accounts have properties called SUM or WAV, depending on property it will calculate normal YTD or WAV.

our problem , here is the earlier script was like

*XDIM_MEMBERSET TIME=BAS(CALCTIME)

because of this there are numbers members for instance 60 by default are going to badi,

and rest account * entity are going..

I tried to put filter on time , such that take changed value from input template, i.e for example if 2015.02 changed, i passed to script logic, but here the cummulative averages should get calculated i.e 2015.03 also changes, which i dont pass. so this case is void.

so i was thinking of option like, get the members changed from input template, and say for example if its 2015.03, i will scope time as 2015.03 to 2015.12 such that all values of YTD will recalculate from 2015.03 to 2015.12.

Please advise..

not sure if user inputs at a time on multiple time periods like 2014.03 and 2015.05 , both are different time periods, if i use existing TMVl, it is not  scoping properly just tested.

Test is like changed value for 2014.03 and 2015.10..

the values after 2014.02 in ytd are incorrect, like wise values of ytd after 2015.09 are in correct.

this is because i am taking 12 off set,,

please if this is feasible

attaching sample input/report form

YTD_calc.xml

[former_member186338 · Accepted solution]

Ups,

It's really very hard to understand your requirements...

Let me try to explain myself:

Each month you enter some average daily production or consumption in the periodic model:

2015.01 10

2015.02 20

etc..

What is the input AUDITTRAIL?

Then you want to calculate weighted average daily production or consumption accumulated from the beginning of the year: SUM(Month_VALUE*Month_Days)/SUM(Month_Days)

Do you need to store the result or you can use custom measure formula? Where do you want to store the result?

Do you need to perform this calculation only for specific accounts?

Why not to perform scoping inside badi?

Etc...

Vadim

[Former Member · Accepted solution]

hi Vadim

sorry if i bothered much.

we are not scoping in badi, bcoz user wants only changed value to be converted to ytd/qtd.

and hence placed in default logic.

just forget about Weighted avg, task done by badi, according to account property..

I am almost approaching solution, the only thing i would like to know is

if user inputs say 2014.02, in my scope i should be able to fetch 2014.02 to 2012.12 months i.e 8months

if 2014.05 changed, i would get scope 2014.05 to 2014.12.

If user changes both year like 2014.03 and 2015.06

i should get 2014.03 to 2014.12 adn 2015.06 to 2015.12 so totally 17 months in scope that willbe passed to BADI.

the main problem is bpc is timing out due to heavy badi execution time. However user wants this to happend.

in existing script, they time as bas ( CALC). Calc is one parent member of time having 60 leaf nodes i.e from 2011.01 to 2015.12. which is increasing loop running time.

I tried to scope only member changed value i.e 2015.03 say, but 2015.04 ytd value should change, hence i need to scope members from 2015.03 to 2015.12 to badi..

if the above is not possible, can get atlease all months of 2015.[of course loop running time increases but only option left]

Also if user changes 2014.03, and 2015.05, ,, i should get all months of 2014,and 2015 if its not pozble to get 2014.03 to 2014.12 , 2015.05 to 2015.12 alone.

Auditrial is a dimension containing Manual, C_ytd,C_QTd members.

user input value goes to Manual

please advise

iam also trying from my end.

[former_member186338 · Accepted solution]

Sorry, "we are not scoping in badi, bcoz user wants only changed value to be converted to ytd/qtd." - absolutely incorrect (misunderstanding what is the scope of custom logic badi).

If you want to work with only changed members you have to write write back badi instead of custom logic badi. Default.lgf is producing incorrect scope (not only changed members!). And you will not improve it by script!

In write back badi you will receive a table of changed records before writing to cube. You can write a code that will read cube data starting from the earliest month in scope till the end of the year. Then perform calculations and save results.

To get an idea about write back badi please read:

Vadim

[Former Member · Accepted solution]

Sure,

but in mean while, could you please advise how can i retrieve Year by the month value changed

?

or months after changed month.

for example if the input to scope is 2015.02, i should 2015, so that i will fetch bas members and pass

[former_member186338 · Accepted solution]

Useless efforts! Sorry!

[Former Member · Accepted solution]

hi Vadim

somehow managed to achieve by using below things....

*SELECT(%YEARSELECT%,"YEAR","TIME","ID='%TIME_SET%'")

*FOR %YRLOOP%=%YEARSELECT%

*XDIM_ADDMEMBERSET TIME = BAS(%YRLOOP%.TOTAL)

*NEXT

[former_member186338 · Accepted solution]

You are going in the wrong direction!

And the script is incorrect:

Assuming %TIME_SET%=2014.02,2014.05,2014.08,2015.03,2015.06

The result of: *SELECT(%YEARSELECT%,"YEAR","TIME","ID='%TIME_SET%'")

Will be: %YEARSELECT%=2014,2014,2014,2015,2015

I order to get unique years:

*SELECT(%YS%,[YEAR],TIME,"[ID]=%TIME_SET%") //%YS%=2014,2014,2014,2015,2015

*SELECT(%UYTS%,[ID],TIME,"[YEAR]=%YS% AND [PERIOD]=TOTAL") //%UYTS%=2014.TOTAL,2015.TOTAL

*SELECT(%UYS%,[YEAR],TIME,"[ID]=%UYTS%")

%UYS%=2014,2015

[former_member186338 · Accepted solution]

You can also scope time from my prev code like:

*XDIM_MEMBERSET TIME =BAS(%UYTS%) //%UYTS%=2014.TOTAL,2015.TOTAL

[Former Member · Accepted solution]

hi Vadim

thanks

i thought, even if multiple values come out, it will take single value..

when i ran in ujkt with my script, it took only single value..

sure, i will try your code as well

[former_member186338 · Accepted solution]

Your issue is not the script code, but the badi code...

For example, if you want to perform the weighted daily average calculation only on some accounts and for the rest - do nothing. In this case if you update one account (with average calculation) and many accounts and periods without this calculation - all time members will be combined in the default.lgf scope, resulting in huge amount of useless calculations:

Vadim