on 02-09-2017 4:47 PM
Hi Experts,
My environment is SAP BPC NW 10.1.
I have the following problem:
My Financial application is on YTD.
I put data on an INC account like that:
2016.01 = 1
2016.02 = 2
2016.03 = 3
2016.04 = 4
When I request my data on PERIODIC Measure, I have these amounts:
2016.01 = 1
2016.02 = 1
2016.03 = 1
2016.04 = 4 => KO
I think that problem come from QUARTER member.
I have tried to fix PERIODIC Custom Measure like that:
1) Solution 1 (I have added Q2, Q3, Q4 member in the Default Periodic):
MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3
MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%PERIOD%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3
2) Solution 2 (I have used script in Attachment from SAP Note)
IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02])),
IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",
IIF( ([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ),
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02])),
IIF( ([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" ),
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]))
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]).LAG(3)),
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]))
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]).LAG(1))
)
),
IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
IIF( ([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ),
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02])),
IIF( ([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" ),
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]))
+([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]).LAG(3)),
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]))
+([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]).LAG(1))
)
),
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIOD%].[LEVEL02]))
)
)
)
' SOLVE_ORDER=3
My amounts are wrong after executed these 2 Custom Measure.
Thanks for help.
Alexandre
Please also read this note: https://launchpad.support.sap.com/#/notes/2136030/E
Also this note: https://launchpad.support.sap.com/#/notes/2401818
But looks like you have to wait for 750 SP07 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Vadim,
My version is:
BW 750 - SP Level 0005 - Support Package - SAPK-75005
BPC 10.1
I don't have any "special" members.
I have removed all values in PARENTH1, save and process.
I have realized following YTD input:
2016.01 = 1
2016.02 = 2
2016.03 = 3
2016.04 = 4
2017.01 = 10
When I request my data on PERIODIC Measure, I have these amounts:
2016.01 = 1
2016.02 = 1
2016.03 = 1
2016.04 = 1
2017.01 = 10
=> all is ok but I don't have any hierarchy relation.
Next, I complete my PARENTH1 with my base levels directly linked with TOTAL:
2016.01 => 2016.TOTAL
....
2016.12 => 2016.TOTAL
=> all is ok.
But when I complete my PARENTH1 with my base levels directly linked with QUARTER:
2016.01 => 2016.Q1
....
2016.12 => 2016.Q4
My PERIODIC amounts are wrong when I create a rapport with base level member only:
2016.01 = 1
2016.02 = 1
2016.03 = 1
2016.04 = 4
But when I add a quarter member in my rapport, my amounts are Ok:
2016.01 = 1
2016.02 = 1
2016.03 = 1
2016.04 = 1
2016.Q1 = 3
It's a crazy thing.
I need to open a ticket on support?
Thanks for help.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
First please provide the detailed info about your BPC and BW version and SP!
Second - looks like your TIME hierarchy is corrupted. Remove PARENTH1 values, save and process, the insert PARENTH1 values - save and process.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Vadim,
Thanks for your answer.
In Attachment an extract of my TIME dimension.
Do I have to fix a property in my dimension (BASE_PERIOD or MONTHNUM)?
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, but don't try to fix measure formula, the default measure formula is absolutely correct!
Please show you time dimension administration screen, looks like your TIME dimension is incorrect (there are strict rules for members and properties of time dimension).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.