Calculate both periodic and YTD results for various formulas via Member Formulas or Script Logic?

[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.

Requirement:- I need to calculate the various formulas /ratios based on filters provided by users.

1.These formulas need to be calculated as both Periodic and YTD.

2. Ratios/Formulas need to be run for current and prior year for actual category and the budget category (passed by user).

3. I do have some calculations which involves different time periods, like one of the example is below:-

inventory ratio for current year:-Current month Actual Inventory/Average of next 3 months Budget Sales. What is best to use here member formula or script logic?

OUPUT:- The report layout that I need to provide is below:-

Can anyone help me with how to get the logic work for both Periodic and YTD calculations for various categories at a time.

Thanks,

Ambika

[former_member186338 · Accepted solution]

Dimension member formula to calculate ratio using YTD values is:

([PL300],[MEASURES].[YTD])/([PL010],[MEASURES].[YTD])

For periodic you will need a separate member with formula:

[PL300]/[PL010]

"inventory ratio for current year:-Current month Actual Inventory/Average of next 3 months Budget Sales. What is best to use here member formula or script logic?"

Use dimension member formula with: [TIME].CURRENTMEMBER.LAG(-1), [TIME].CURRENTMEMBER.LAG(-2), [TIME].CURRENTMEMBER.LAG(-3) to calculate average of next 3 months

[former_member493004 · Accepted solution]

Thank you Vadim for your reply.

I am going to try this code.

Regards,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

Just post your test results...

[former_member493004 · Accepted solution]

Hi Vadim,

Thank you for your reply.

I tried using the below formula to calculate the average of next three months for one of the calculation.

([31121],[TIME].CURRENTMEMBER.LAG(-1),[31121],[TIME].CURRENTMEMBER.LAG(-2),[31121],[TIME].CURRENTMEMBER.LAG(-3))

in the below screenshot, D10 should be the result (average of next three months of account 31121)

but I got the result as in cell C13.

please advise.

[former_member186338 · Accepted solution]

ambika.raina

Sorry, but:

([31121],[TIME].CURRENTMEMBER.LAG(-1),[31121],[TIME].CURRENTMEMBER.LAG(-2),[31121],[TIME].CURRENTMEMBER.LAG(-3))

is meaningless...

Has to be tuple1 + tuple2 + ...

([31121],[TIME].CURRENTMEMBER.LAG(-1))+([31121],[TIME].CURRENTMEMBER.LAG(-2))+([31121],[TIME].CURRENTMEMBER.LAG(-3))

[former_member493004 · Accepted solution]

also I tried the code below for formula but did not get the expected result.

"inventory ratio for current year:-Current month Actual Inventory/Average of next 3 months Budget Sales"

but here I need to give dynamic version member instead of budget_v4 so that it can work based on the version providded by the user. (like %version_set% as we give in script logic).

[T1130]/([T3111],[VERSION].[BUDGET_V4],[TIME].CURRENTMEMBER.LAG(-1),[T3111],[VERSION].[BUDGET_V4],[TIME].CURRENTMEMBER.LAG(-2),[T3111],[VERSION].[BUDGET_V4],[TIME].CURRENTMEMBER.LAG(-3))

[former_member186338 · Accepted solution]

ambika.raina

Sorry, but also meaningless...

Tuple is combination of dimension members like:

([dim1].[m11],[dim2].[m21])

You have to fix [VERSION].[ACTUAL] in the formula an select required budget version in the report

3*([VERSION].[ACTUAL],[T1130])/(([T1130],[TIME].CURRENTMEMBER.LAG(-1))+([T1130],[TIME].CURRENTMEMBER.LAG(-2))+([T1130],[TIME].CURRENTMEMBER.LAG(-3)))

P.S. Corrected - added ()

[former_member493004 · Accepted solution]

HI Vadim,

Thanks again for your help.

I corrected the formula and got correct results for avg for next three months.

but for the inventory formula where in the same formula, I have two different categories involved, I could not get the results correctly.

formula for actual is :Current month Actual Inventory/Average of next 3 months Budget Sales

I tried below code for above formula:-

([T1130],[VERSION].[ACTUAL])/(([T3111],[TIME].CURRENTMEMBER.LAG(-1))+([T3111],[TIME].CURRENTMEMBER.LAG(-2))+([T3111],[TIME].CURRENTMEMBER.LAG(-3)))/3

test results are below. the expected result should post in ACTUAL version itself but it is getting posted under Budget version.

Also formula for budget version is: Current month BUDGET Inventory/Avg of next 3 months Budget Sales.

So here I am not sure with one account having different formula for actual and budget versions how to get the proper results.

Please advise.

Thanks,

Ambika

[former_member493004 · Accepted solution]

Hi Vadim,

Here is update on my testing for various formulas, I am able to get the ratios by using member formulas.

I have now one formula left to implement: CURRENT Month Actual Inventory / AVG of Next 3 months Budgeted Sales.

as this formula involves two versions actual and budget and result should be posted to Actual version, can you please advise as how can I get this to work.

Thanks,

Ambika

[former_member493004 · Accepted solution]

Hi Vadim,

Any help on my query :-

I have now one formula left to implement: CURRENT Month Actual Inventory / AVG of Next 3 months Budgeted Sales.

as this formula involves two versions actual and budget and result should be posted to Actual version, can you please advise as how can I get this to work.

Thanks,

Ambika

[former_member186338 · Accepted solution]

Sorry, but try to do something yourself. If you want me to create all formulas for you then may be your salary have to be transferred to my account 🙂

Current month Actual:

([T1130],[VERSION].[ACTUAL])

Next month budget:

([T3111],[TIME].CURRENTMEMBER.LAG(-1),[VERSION].[BUDGET])

If some dimension is not specified in formula then the member is coming from EPM context/page/row/column intersection.

[former_member493004 · Accepted solution]

Hi Vadim,

Sorry I think I gave you the wrong impression. I did write the logic and tested it but the result I got is showing under the Budget version. Instead I need to see the results always under Actual version for this formula. That's why I tried to seek your help.

([T1130],[VERSION].[ACTUAL])/(([T3111],[TIME].CURRENTMEMBER.LAG(-1))+([T3111],[TIME].CURRENTMEMBER.LAG(-2))+([T3111],[TIME].CURRENTMEMBER.LAG(-3)))/3

Based on the above code, I got below result:-

The result 0.1433 I need to see under ACTUAL version.

but result under actual version 0.1721 is the ratio of ACTUAL inventory/AVG of Actual sales (manually calculated and checked).

Below is the data based on which I should get 0.143 under ACTUAL version.

Thanks,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

Please, read again my last comment!

Is it clear?

[former_member493004 · Accepted solution]

Thank You Vadim 🙂

I am able to fix the formula.

Thanks again for all your help 🙂

Regards,

Ambika

[former_member186338 · Accepted solution]

ambika.raina

Then, please accept the correct answer!