YTD calculations in periodic model for various formulas

[former_member493004]

Hello ,

Product version: BPCNW 10.1 Classic

In the model Planning, we have dimensions TIME,VERSION,ACCOUNT,COMPANY_CODE,PROFIT CENTER,AUDIT TRAIL,CURRENCY.

This model is periodic.

Script to be launched by Data Manager package.
Logic: when user passes the time, entity, version via DMP, I want to calculate some accounts based on some formulas like one formula is Gross profit/Sales = GP margin. this calculation is working fine for measures periodic but I need to get the results for Year to date data as well.

I have written the script which is working for periodic calculations I am not sure as how in periodic model I can get the calculations for YTD as well..

a snapshot of the working logic is below:-

can anyone help me with how to get the logic work for YTD numbers.

*XDIM_MEMBERSET AUDIT_TRAIL = BAS(ALL_AUDIT_TRAIL)
*XDIM_MEMBERSET COST_CENTER = BAS(ALL_COST_CENTER)
*XDIM_MEMBERSET CURRENCY = LC,USD
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET FLOW = BAS(END)
*XDIM_MEMBERSET PROFITCENTER = BAS(ALL_PC)
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET VERSION = ACTUAL, %VERSION_SET%
/...............START logic for ACTUAL version...........
*WHEN ACCOUNT
            *IS SALES
            *REC(EXPRESSION =[ACCOUNT].[GP]/[ACCOUNT].[SALES],ACCOUNT = GP_Margin, AUDIT_TRAIL=BPC_CACLS)
            *REC(EXPRESSION =
[ACCOUNT].[OP]/[ACCOUNT].[SALES],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN


 

[former_member186338 · Accepted solution]

Let's do a test in ENVIRONMENTSHELL:

Script:

*XDIM_MEMBERSET ACCOUNT = PL010
*XDIM_MEMBERSET CATEGORY=Actual
*XDIM_MEMBERSET TIME=2017.05
*XDIM_MEMBERSET MEASURES=PERIODIC //Required!!! Has to be model storage type.

*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=([ACCOUNT].[PL300],[MEASURES].[YTD])/([ACCOUNT].[PL010],[MEASURES].[YTD]),ACCOUNT=PL999)
*ENDWHEN

Resulting report:

PL999 is properly calculated based on YTD values.

But in general, it's better to use dimension formulas to calculate ratios.

[former_member493004 · Accepted solution]

Thank you Vadim.

I will try this code for my calculations 🙂

thanks,

Ambika

[former_member493004 · Accepted solution]

Hi Vadim,

thank you for your reply and helping me.

I tried this code for one of my calculation and its working but one more clarification here is that user wants calculations in both periodic and YTD as well.

for eg: I am calculating GP margin, OP margin then I need to show two results one periodic and one YTD. does it mean I need to create two separate accounts for each of my results (periodic and YTD both) as I have about 19 calculations. please suggest.

Thanks,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

I need to create two separate accounts for each of my results - YES!

By the way, I have already told you: use dimension member formulas instead of script logic for ratios.

With script logic you will have incorrect results on parent nodes

[former_member493004 · Accepted solution]

Hi Vadim,

so for periodic I can use member formulas but for getting YTD results ,can it be calculated by member formulas...Please advise.

Thanks,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

Please accept the correct answer!

[former_member493004 · Accepted solution]

Hi Vadim,

Before I close this post/query, can you please help me with the below queries:-

1. Actually my logic for ratios needs to be run for current and prior year for actual category and the budget category (passed by user).

2. User needs to see both periodic and YTD values for all the margins.

I got that periodic numbers, I can get from member formulas but how to get YTD values and the calculations for different categories.

Please advise.

Thanks,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

Sorry, but your original question about script logic to calculate value based on YTD results was answered.

And the correct answer have to be accepted.

If you want help on dimension member formulas - ask a new question and I will provide the formula.

[William_Yu1]

Hi ambika.raina,

Please have a look at this post: https://answers.sap.com/questions/9001887/measures-in-script-logic.html.

Best regards, William

[former_member186338]

Unfortunately, the provided link contain some misleading info...