ROY BPC Custom Measure

Hi,

We are on BPC 7.5, Netweaver base BW 7.4

We use currently a standard YTD measure using period as follows;

MEMBER [MEASURES].[YTD] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

and I would like to create a custom ROY(Rest of Year (future Periods)) measure in which will be able sum next periods while you are on current period.

Please advise on how I can modify the above formula to cater for my requirements.

Description : Rest of the Year

Formula name : ROY

Thanks,

Themba

SAP Managed Tags:
SAP Business Planning and Consolidation, version for SAP NetWeaver

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi Themba,

Just to check:

"custom ROY(Rest of Year (future Periods)) measure in which will be able sum next periods while you are on current period."

equivalent to:

= Year.TOTAL - YTD?

Vadim

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi.

The year to date measure looks at past periods if you are running a report for a current. Our requirement is have a similar measure where we run a report for a current month but the measure will sum data that lying in future periods or subsequent periods.

Your help will be much appreciated.

Thanks,

Themba

Sorry, but please read once again my previous message!

Sample:

TIME 2015.JUN with MEASURE YTD = 200

TIME 2015.TOTAL with MEASURE YTD or PERIODIC= 800

TIME 2015.JUN with MEASURE ROY = 800-200=600 - isn't it?

Vadim

Something like:

MEMBER [MEASURES].[ROY] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Hi,

See attached sample.

The assumption is that I run the report in the current month - June.

I hope this clarify the issue

Sorry, but I have to write you third time - the remaining is equal to total minus YTD.

And forget about mixing of 2 categories - Actual and Budget!

For the ROY report it's better to create a special category like Forecast...

Vadim

Hi,

We already have a special category but we combine this data into one category. So our requirement is to have a report where we run June data with ROY measure and it will sum subsequent data in periods in June and in one forecast category.

Please help.

Thanks,

Themba

If you copy Actual and Budget into single Forecast category - then - read my previous answers. We use this method of ROI calculation for already 3 years.

Vadim

Hi Vadim,

So this method sums up future periods?

Please advise,

Thanks,

Themba

Sorry, but you don't understand me...

Vadim

Good Day Vadim,

Can you please explain it to me so I can understand.

Thanks,

Themba

Not sure it's possible... I have done it number of times with a sample etc.. But you still talk about "sums up future periods"

Hi Vadim,

Now I get you and I have tried the above method and it does give me the sum of future periods as I want it but excludes the current period. How can I make it to include the current period.

Thank you very much for your help thus far,

Themba

For sure you can always add the value of the current period to the result: Year.TOTAL - CurrentMonth.YTD + CurrentMonth.Periodic

But from the financial point of view ROY is not including the current month... This value is meaningful for:

1. You close some month - for example May. You have Actual for May and prev months.

2. You copy to Forecast Jan-May

3. For Jun-Dec you copy something - previous forecast or budget.

4. Finance planners will adjust figures for Jun-Dec to prepare updated forecast version.

5. Then for May you have YTD (based on actual) and ROY (based on forecast Jun-Dec).

Vadim

Hi Vadim,

Thank you very much for your help.

I have changed the method to be as follows and it does give me the current value as well.

MEMBER [MEASURES].[ROY] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

But now for average numbers (ROY_AVG)

Please help with the method. I have the below method but it does not work

Your help in this regard would be much appreciated.

Thanks,

Themba

"But now for average numbers (ROY_AVG)" - sorry, but I do not understand the business value of ROY_AVG!

ROY - clear - what have to be done to achieve the year end forecast! But ROY_AVG???

ROY_AVG is for the same purpose, the difference is that ROY_AVG will be used when calculate labour numbers, units.

For example....average number of employees over certain period. We cannot sum those values because they will double up.

See below sample:

Your help will be much appreciated

Themba

Please show the desired ROY_AVG for all months in this sample!

Hi Vadim,

See the sample below - based on the example that I run the report in June and it should give me the average of the future periods including June as an average value.

Thanks for your help.

Regards,

Themba

You run the report for ALL months - is it clear?? Not for June!

Can you show the line for all months?

P.S. If you have ROY you can use property BASE_PERIOD of time dimension containing numeric month number... you can divide...

Vadim

No, I would run the report for June only with the expectation of results showing average of future periods including June.

Row 21 below highlighted in Yellow is all the months for the sample below:

Thanks,

Themba

Sorry, but if you are talking about custom measure than it have to work for all months

Anyway, look on the BASE_PERIOD property... Hope it's clear how to use it!

Hi,

Sorry I think there is a misunderstanding. The custom measure will have to work for all the months. I specifically said June because of the sample. Please advise if there is a method we can use.

The Base_Period property is not clear.

I would prefer to use the method on the custom measure.

Thanks,

Themba

"The Base_Period property is not clear."

Ups... it's so simple!

You have ROI including current month June

BASE_PERIOD of June = 6

12-6+1=7

ROI/7=ROI_AVG

Thanks...

That could work.

Can you please put it in a formula for me. Help with the syntax formula

Regards,

Themba

Sorry,

Try to do at least something yourself!

Something like:

[TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD")...

Vadim

Hi Vadim,

I am not really familiar with SQL.

Nonetheless, I am tried to formuale the syntax and I did not come alright.

I am battling to combine the ROY with the additional sytanx to calculate the average value.

Your help will be much appreciate.

Thanks,
Themba

It's not SQL, it's MDX. Read help on Microsoft site! Please!

Vadim

Yes, i know it is an mdx syntax.

I am reading the on microsoft site but I am still battling.

Please help.

Thanks,

Themba

Continue battling! At least you will get some knowledge instead of copy/paste!

Vadim

Good Day Vadim,

Kindly help, I have tried the below syntax as advise by you but it does not seem to work.

MEMBER [MEASURES].[ROY_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA])/([%TIME%].LASTCHILD.PROPERTIES("2/CPMB/XTPHXFG")-[%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")+1),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),[MEASURES].[/CPMB/SDATA])/([%TIME%].LASTCHILD.PROPERTIES("2/CPMB/XTPHXFG")-[%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")+1),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Your help will be much appreciated

I am talking about [TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD") - only this property is numeric, the rest are strings and can't be used in the arithmetic formula. The only thing I am not 100% sure - do you have this property in BPC 7.5!

Vadim

Hi,

I could not pick up BASE_PERIOD in BPC 7.5 on our property list for TIME hence I am using the below property called MONTHNUM:

Hence I am [TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")+

see below list of properties for time we have on our system:

Your help would be much appreciated.

Thanks,

Themba

Ups, BASE_PERIOD is missing in BPC 7.5 and it's the only numeric type attribute...:

Look on MONTHNUM in RSA1 in BPC 7.5:

And look on BASE_PERIOD in BPC 10:

The only way - play with long IIF(...,IIF(...

Vadim

Hi Vadim,

Can you please give an example of IIF(...,IIF(...

Thanks,

Themba

Like:

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",SOMETHING/1,

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="2",SOMETHING/2,

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="3",SOMETHING/3,

...

Vadim

Hi,

I dont understand this syntax - "SOMETHING/1" and how would I combine it with the ROY formula.

Thanks,

Themba

SOMETHING is some formula...

Hi,

Will it convert it to a number?

Please advise,

Thanks,

Themba

It what?

You have to test property for each string like "1","2"... and for each result divide by 1,2,3 etc...

Hope it's clear!

It being a property because now it is stored as character.

So question is will this syntax convert the character to a numeric value?

"So question is will this syntax convert the character to a numeric value?" - definitely NO!

And there is no way to convert string to numeric!

Vadim

Hi,

Now the syntax is not clear because I dont understand how it will work and combine with the rest of the method.

Can you kindly elaborate on that.

Thanks,

Themba

Sorry, I can't explain more clearly how to use multiple nested IIF!

IIF has 3 parameters - condition, expression if true, expression if false

You can nest IIF inside IIF to test multiple conditions.

What is not clear here???

Hi,

What is not clear is that I dont know what IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",SOMETHING/1 syntax will do.

Please advise,

Thanks,

Themba

I am unable to help you...

May be reading of standard BPC 7.5 help will help?

Advanced Rule Formula Examples - SAP BusinessObjects Planning and Consolidation - SAP Library

Number of examples with IIF

Hi Vadim,

I have tried to the below formula....

MEMBER [MEASURES].[ROY_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA])/([%TIME%].2015.SEP.PROPERTIES("2/CPMB/XTPHXFG"))-(IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",SOMETHING/1,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="2",SOMETHING/2,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="3",SOMETHING/3,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="4",SOMETHING/4,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="5",SOMETHING/5,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="6",SOMETHING/6,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="7",SOMETHING/7,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="8",SOMETHING/8,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="9",SOMETHING/9,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="10",SOMETHING/10,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="11",SOMETHING/11,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="12",SOMETHING/12,0))))))))))))+1)),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),[MEASURES].[/CPMB/SDATA])/([%TIME%].2015.SEP.PROPERTIES("2/CPMB/XTPHXFG"))-(IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",SOMETHING/1,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="2",SOMETHING/2,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="3",SOMETHING/3,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="4",SOMETHING/4,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="5",SOMETHING/5,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="6",SOMETHING/6,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="7",SOMETHING/7,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="8",SOMETHING/8,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="9",SOMETHING/9,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="10",SOMETHING/10,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="11",SOMETHING/11,IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="12",SOMETHING/12,0))))))))))))+1)),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

But I am unable to save the formula....

Kindly have a look.

Thanks,

Themba

Hi,

I am getting the below while trying to save

Hi Vadim,

Did you manage to have a look at the issue for me?

Please help.

Thanks,

Themba

Sorry, but it's so simple that you have to correct it yourself! Hint - SOMETHING - is some expression...

Ask a Question

Top Q&A Solution Author

User

Count