Dynamic Addition of dimension members of time dimension using logic script

[Former Member]

Hi Experts,

I am writing a logic script to calculate the average value of current period and past three period. I have written logic for that. But I am facing issue in that. First I share the requirement.

User will select the Version and Entity in DMP. Based on the version selected in DMP, the dimension member of time will be derived (Note: Time is one of the property of Version Dimension. Based on the entity selected in DMP, segment will be driven. This was the part of DMP.

There are following dimensions in the model i.e. DataSrc, Account, Version, Time, RptCurrency, Entity.

DataSrc: Input

Account: 20001113, 2000114

Version: Actual

Time: this is being driven by time property of the version selected in DMP, which eventually referred in script logic.

RptCurrency: LC

Entity: Member will be selected from DMP, which eventually called in Script Logic

Segment: This will be driven by segment property of Entity.

How logic script would work is: it will take the time dimension member from time property of Version Dimension and will be added with past three period lets say, User selects PLAN_1 in DMP and runs it. Time Property value is 2016.09 (last period of qtr). Data of the time (2016.09) will add with data of time 2016.06, 2016.03. 2015.12. in the end, the average value (Total/4) will be posted to different Data Srs.

(User selects PLAN_1 version just to derive the time only) but in logic script, version will be Actual).

Attached in txt file is the logic which I have written. Issue is this logic is fetching data of first period and last period and is skipping middle two periods. I don't know why.

Regards,

CALC_AVG.txt.zip

[former_member186338 · Accepted solution]

Bad idea...

Use:

*SELECT(%SEGMENTID%,SEGMENT,ENTIY,"[ID]='%ENTITY_SETT%'")

*SELECT(%TIMEID%,TIME,VERSION,ID=%VERSION_SET%)

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET SEGMENT = %SEGMENTID%

*XDIM_MEMBERSET TIME = %TIMEID%,TMVL(-3,%TIMEID%),TMVL(-6,%TIMEID%),TMVL(-9,%TIMEID%)

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET DATASRC = INPUT

*XDIM_MEMBERSET ACCOUNT = 20001113, 20001114

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%/4,TIME = %TIMEID%,DATASRC=CALC_AVG)

*ENDWHEN

Vadim

P.S. Can you explain the business requirements of this script?

[Former Member · Accepted solution]

Vadim, you idea resolved my issue. thanks.

But I still want to know that what went wrong in my logic script, in which I use Look Up command. I looked up three time dimension member TIME1, TIME2, and TIME3 (as attached in text file in first message), But output of my script was showing total for %TIMEID% and last period i.e. TIME3 and getting divided by 4.

Regards,

[former_member186338 · Accepted solution]

No idea without detailed info including UJKT log. But the script is wrong in general:

If for some reason %TIMEID% value is missing (no record) then calculation will not happen at all even if you have values in TMVL(-3,%TIMEID%),TMVL(-6,%TIMEID%),TMVL(-9,%TIMEID%).

Vadim

[Former Member · Accepted solution]

%TIMEID% value is populating in UJKT. What is happening

I assigned parameter VERSION=PLAN_1 in UJKT

Time Property value associated with PLAN_1 is 2016.06. thus, %TIMEID% will be 2016.06, TIME1 (which is TMVL(-3,%TIMEID%) will be 2016.03, TIME2 (which is TMVL(-6,%TIMEID%) will be 2015.12 and same would for TIME3 = 2015.09. The same time dimension members populate when I validate and execute the script.

time member ids are appearing, but transaction data associated to those time IDs are only coming for 2016.06 and 2015.09, but for other middle two time IDs 2016.03 and 2015.12, data is coming zero.

for testing purpose, I stored 100 for 2016.06, 200 for 2016.03, 300 for 2015.12, and 400 for 2015.09 by input template. Result must be 250 ((100+200+300+400)/4) after running scrip logic.

But in my case of Look Up, I am able to see 100 for 2016.06, 0 for 2016.03, 0 for 2015.12, and 400 for 2015.09. Result is coming 125 ((100+0+0+400)/4). Lookup(TIME1) and Lookup(TIME2) are not able to fetch data from model.

Regards,

[former_member186338 · Accepted solution]

Log?