on 06-18-2015 3:19 PM
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
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.
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
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
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
MEMBER [MEASURES].[ROY_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-AVG(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-AVG(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
Your help in this regard would be much appreciated.
Thanks,
Themba
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
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 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
User | Count |
---|---|
15 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.