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
5 Answers
-
Feb 09, 2017 at 04:59 PM
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).
Add comment
1 Comment-
The correct report for YTD cube have to be like:
-
-
Feb 09, 2017 at 11:04 PM
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
Add comment
-
Feb 10, 2017 at 07:21 AM
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.
Add comment
-
Feb 10, 2017 at 10:34 AM
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.
Add comment
-
Feb 10, 2017 at 10:44 AM
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 :)
Add comment
Add comment