Logic script to convert Periodic to YTD not considering Zero value records

[Former Member]

Hi All,

I am writing a Logic script (also tried using WHEN_REF_DATA = MASTER_DATA in between XDIM_MEMBERSET (to restrict scope and WHEN ...ENDWHEN construct

If there is a record for period 2015.FEB  with value Zero, then it is not writing any new record for that .. But if the value exists for all the periods, this logic script works and creates a new record with the cumulative value

*XDIM_MEMBERSET ACCOUNT = 880100

*XDIM_MEMBERSET COMPANY = 100000

*XDIM_MEMBERSET INTERCO = I_NONE

*XDIM_MEMBERSET PC = P1020

*WHEN_REF_DATA = MASTER_DATA

*WHEN AUDITTRAIL

*IS PLAN_M

*WHEN TIME

*IS 2015.JAN

*REC(EXPRESSION = [TIME].[2015.JAN] ,TIME = 2015.JAN,AUDITTRAIL = REG_ADJ, PC = P1020)

*ENDWHEN

*WHEN TIME

*IS 2015.FEB

*REC(EXPRESSION = [TIME].[2015.JAN] + [TIME].[2015.FEB] ,TIME = 2015.FEB,AUDITTRAIL = REG_ADJ, PC = P1020)

*ENDWHEN

*WHEN TIME

*IS 2015.MAR

*REC(EXPRESSION = [TIME].[2015.JAN] + [TIME].[2015.FEB] + [TIME].[2015.MAR],TIME = 2015.MAR,AUDITTRAIL =  REG_ADJ, PC = P1020)

*ENDWHEN

*WHEN TIME

*IS 2015.APR

*REC(EXPRESSION = [TIME].[2015.JAN] + [TIME].[2015.FEB] + [TIME].[2015.MAR] + [TIME].[2015.APR],TIME = 2015.APR,AUDITTRAIL =  REG_ADJ, PC = P1020)

*ENDWHEN

*ENDWHEN

Many Thanks

Krishna

[former_member186338 · Accepted solution]

Use push instead of pull for values!

*WHEN TIME

*IS 2015.JAN

*REC(EXPRESSION=%VALUE%,TIME = 2015.JAN,AUDITTRAIL =  REG_ADJ)

*REC(EXPRESSION=%VALUE%,TIME = 2015.FEB,AUDITTRAIL =  REG_ADJ)

*REC(EXPRESSION=%VALUE%,TIME = 2015.MAR,AUDITTRAIL =  REG_ADJ)

...

*REC(EXPRESSION=%VALUE%,TIME = 2015.DEC,AUDITTRAIL =  REG_ADJ)

*IS 2015.FEB

*REC(EXPRESSION=%VALUE%,TIME = 2015.FEB,AUDITTRAIL =  REG_ADJ)

*REC(EXPRESSION=%VALUE%,TIME = 2015.MAR,AUDITTRAIL =  REG_ADJ)

...

*REC(EXPRESSION=%VALUE%,TIME = 2015.DEC,AUDITTRAIL =  REG_ADJ)

*IS 2015.MAR

...

*ENDWHEN

Vadim

[Former Member · Accepted solution]

Hi Vadim,

thanks for the reply.

I will try this , can I know what the %VALUE% is for in your statements please?

Thanks

Krishna

[former_member186338 · Accepted solution]

%VALUE% is a value of the current record.

*REC(EXPRESSION=%VALUE%,...)

is equivalent to:

*REC(FACTOR=1,...)

Vadim

[Former Member · Accepted solution]

Hi Vadim, I tried that way too, but still it does not consider Zero value records...I tried the following as well...2015.JAN does not have any value...it does not write the record... from 2015.MAR there is non zero value, so for period 2015.mar it creates new record. For 2015.JUN again there is ZERO record, it does not sum until May, does not create any new record.

*XDIM_MEMBERSET ACCOUNT = 880100

*XDIM_MEMBERSET COMPANY = 100000

*XDIM_MEMBERSET INTERCO = I_NONE

*XDIM_MEMBERSET PC = P1020

*WHEN AUDITTRAIL *IS PLAN_Y

*WHEN TIME *IS 2015.JAN *REC(EXPRESSION =0, TIME = 2015.JAN,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(EXPRESSION = %VALUE% ,TIME = 2015.JAN,AUDITTRAIL = INPUT_YTD, PC = P1020) *ENDWHEN

*WHEN TIME *IS 2015.FEB *REC(EXPRESSION =0, TIME = 2015.FEB,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(EXPRESSION = %VALUE% ,TIME = 2015.FEB,AUDITTRAIL = INPUT_YTD, PC = P1020) *ENDWHEN

*WHEN TIME *IS 2015.MAR *REC(EXPRESSION =0, TIME = 2015.MAR,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(EXPRESSION = %VALUE% ,TIME = 2015.MAR,AUDITTRAIL = INPUT_YTD, PC = P1020) *ENDWHEN

*WHEN TIME *IS 2015.APR *REC(EXPRESSION =0, TIME = 2015.APR,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(FACTOR = %VALUE% ,TIME = 2015.APR,AUDITTRAIL = INPUT_YTD , PC = P1020 ) *ENDWHEN

*WHEN TIME *IS 2015.MAY *REC(EXPRESSION =0, TIME = 2015.MAY,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(FACTOR = %VALUE% ,TIME = 2015.MAY,AUDITTRAIL = INPUT_YTD , PC = P1020 ) *ENDWHEN

*WHEN TIME *IS 2015.JUN *REC(EXPRESSION =0, TIME = 2015.JUN,AUDITTRAIL = INPUT_YTD, PC = P1020) *REC(FACTOR = %VALUE% ,TIME = 2015.JUN,AUDITTRAIL = INPUT_YTD , PC = P1020 ) *ENDWHEN

*ENDWHEN

Please help

Regards Krishna

[former_member186338 · Accepted solution]

Sorry, please look again on my proposed  script!

It's completely different to what you try to execute!

The correct one:

*XDIM_MEMBERSET ACCOUNT = 880100

*XDIM_MEMBERSET COMPANY = 100000

*XDIM_MEMBERSET INTERCO = I_NONE

*XDIM_MEMBERSET PC = P1020

*XDIM_MEMBERSET AUDITTRAIL=PLAN_Y

*XDIM_MEMBERSET TIME=BAS(2015.TOTAL)

*WHEN TIME

*IS 2015.JAN

*REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUL,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.AUG,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.SEP,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.OCT,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.NOV,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.DEC,AUDITTRAIL=INPUT_YTD)

*IS 2015.FEB

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUL,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.AUG,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.SEP,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.OCT,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.NOV,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.DEC,AUDITTRAIL=INPUT_YTD)

*IS 2015.MAR

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUL,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.AUG,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.SEP,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.OCT,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.NOV,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.DEC,AUDITTRAIL=INPUT_YTD)

...

*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

Not necessary to have PC = P1020:

*REC(EXPRESSION =0, TIME = 2015.JAN,AUDITTRAIL = INPUT_YTD, PC = P1020)

Because you scope:

*XDIM_MEMBERSET PC = P1020

And:

EXPRESSION =0 - is also strange

Vadim

[Former Member · Accepted solution]

Thanks Vadim, still not able to write data for Zero periods...

This is my source...

W_NONE 880100 P1020 2015.APR 100000 I_NONE F15 PLAN_Y ACTUAL GBP S_NONE 21,426.00

W_NONE 880100 P1020 2015.MAR 100000 I_NONE F15 PLAN_Y ACTUAL GBP S_NONE 21,426.00

W_NONE 880100 P1020 2015.MAY 100000 I_NONE F15 PLAN_Y ACTUAL GBP S_NONE 36,273.00

when I run the script..I get the following Log. ENDWHEN ACCUMULATION: 5  RECORDS ARE GENERATED. DATA TO WRITE BACK:

ACCOUNT AUDITTRAIL CATEGORY COMPANY CONSOSCOPE CURRENCY FLOW INTERCO PC TIME WBS SIGNEDDATA

880100 INPUT_YTD ACTUAL 100000 S_NONE GBP F15 I_NONE P1020 2015.JAN W_NONE 79125.00

880100 INPUT_YTD ACTUAL 100000 S_NONE GBP F15 I_NONE P1020 2015.FEB W_NONE 79125.00

880100 INPUT_YTD ACTUAL 100000 S_NONE GBP F15 I_NONE P1020 2015.MAR W_NONE 79125.00

880100 INPUT_YTD ACTUAL 100000 S_NONE GBP F15 I_NONE P1020 2015.APR W_NONE 57699.00

880100 INPUT_YTD ACTUAL 100000 S_NONE GBP F15 I_NONE P1020 2015.MAY W_NONE 36273.00

for June and July periods, there is no value , I am expecting it sums until May and writes into June (as June is Zero)

5  RECORDS HAVE BEEN WRITTEN BACK. WRITING TIME :4.00  ms.

SCRIPT

*WHEN TIME *IS 2015.JAN

*REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD) *IS 2015.FEB *REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD) *IS 2015.MAR *REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD) *REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD) *IS 2015.APR *REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*IS 2015.MAY

*REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*IS 2015.JUN

*REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUN,AUDITTRAIL=INPUT_YTD)

*IS 2015.JUL

*REC(EXPRESSION=%VALUE%,TIME=2015.JAN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.FEB,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.APR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAR,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.MAY,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUN,AUDITTRAIL=INPUT_YTD)

*REC(EXPRESSION=%VALUE%,TIME=2015.JUL,AUDITTRAIL=INPUT_YTD)

*ENDWHEN

[former_member186338 · Accepted solution]

Sorry, again you are changing my script to something absolutely incorrect! Why???

The logic is simple:

JAN has to copied to 12 months: JAN-DEC

FEB has to copied to 11 months: FEB-DEC

MAR has to copied to 10 months: MAR-DEC

...

All the values are aggregated in result:

JAN: JAN

FEB: JAN+FEB

MAR: JAN+FEB+MAR

...

Is it clear?

[Former Member · Accepted solution]

Hi Vadim,

you said right. The logic you provided is working as desired.

Many Thanks for your help.

Cheers