Question Re: Script logic to subtract values nwbpc10 in Financial Management Q&A

Script logic to subtract values nwbpc10

Former Member — Tue, 27 Oct 2015 09:59:24 GMT

Hi Experts,

I am new to this script logics. and I am trying to implement a script logic to subtract values from one period to another period. for Ex

Time 2014.p09 2014.P09 2014.P10

Category Decfcast Actual Q4f

Audittrail Input Sap-BW Input

Result 100 70 30(100-70)

From the above, i want to post the differences between Decfcst and actual into Q4f forecast.

First i just take some static periods and try the below code,however this script failed to do subtract , i got the value 100 instead of 30 ,in 2014.P10.

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2197)

*XDIM_MEMBERSET TIME=2014.P09

*XDIM_MEMBERSET CATEGORY=DECFCAST,ACTUAL

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET AUDITTRAIL=SAP-BW,Input

*WHEN ACCOUNT

*IS *

*WHEN CATEGORY

*IS DECFCAST

*REC(EXPRESSION=%VALUE%-([CATEGORY].[ACTUAL],[TIME].[2014.P09]),TIME=2014.P11,CATEGORY=Q4F,AUDITTRAIL=Input)

*ENDWHEN

PFA for package log calculation.

Please help

Re: Script logic to subtract values nwbpc10

former_member186338 — Tue, 27 Oct 2015 10:12:23 GMT

You get 100 because you missed AUDITTRAIL here: ([CATEGORY].[ACTUAL],[TIME].[2014.P09])

Has to be:

([CATEGORY].[ACTUAL],[TIME].[2014.P09],[AUDITTRAIL].[SAP-BW])

And the scope was incorrect, has to be:

*XDIM_MEMBERSET CATEGORY=DECFCAST

*XDIM_MEMBERSET AUDITTRAIL=Input

Re: Script logic to subtract values nwbpc10

former_member186338 — Tue, 27 Oct 2015 10:21:13 GMT

But I recommend to use different logic with aggregation in target:

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2197)

*XDIM_MEMBERSET TIME=2014.P09

*XDIM_MEMBERSET CATEGORY=DECFCAST,ACTUAL

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET AUDITTRAIL=SAP-BW,Input

*WHEN CATEGORY

*IS DECFCAST

*WHEN AUDITTRAIL

*IS Input

*REC(EXPRESSION=%VALUE%,TIME=2014.P10,CATEGORY=Q4F)

*ENDWHEN

*IS ACTUAL

*WHEN AUDITTRAIL

*IS SAP-BW

*REC(EXPRESSION=-%VALUE%,TIME=2014.P10,CATEGORY=Q4F,AUDITTRAIL=Input) //Negative!

*ENDWHEN

Vadim

Re: Script logic to subtract values nwbpc10

Former Member — Tue, 27 Oct 2015 11:04:40 GMT

Hi Vadim,

Thank you so much for your reply.

I have implemented the script what you have recommended and it works..

One more query, Here i have set target as 2014.P10. how to write the script if i use time id as dynamic? I mean if run the same script for time 2015.

Thanks.

Regards,

Srikant

Re: Script logic to subtract values nwbpc10

former_member186338 — Tue, 27 Oct 2015 11:11:12 GMT

If by "dynamic" you are talking about user prompt for source or target month then:

Let's assume you prompt for target and the selected target month will be in %TIME_SET%. Then:

...

*XDIM_MEMBERSET TIME=TMVL(-1,%TIME_SET%) //source is previous month

...

*REC(EXPRESSION=%VALUE%,TIME=%TIME_SET%,CATEGORY=Q4F)

...

*REC(EXPRESSION=-%VALUE%,TIME=%TIME_SET%,CATEGORY=Q4F,AUDITTRAIL=Input)

If you want to allow user to enter multiple target months - then you will have to use FOR/NEXT loop

Vadim