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
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).
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
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.
P.S. Hope you don't have any "special" members in TIME dimension (not month, quarter or year)
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.
Please test the same in a fresh copy of ENVIRONMENTSHEL! Using Consolidation Model.
Also you can upgrade to 750 SP06 to test if this bug is corrected.
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 :)