Skip to Content
0

Custom Measure PERIODIC not allowed to have good amounts after YTD input

Feb 09, 2017 at 04:47 PM

178

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Vadim Kalinin Feb 09, 2017 at 04:59 PM
0

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).

Show 1 Share
10 |10000 characters needed characters left characters exceeded

The correct report for YTD cube have to be like:

per.png (14.0 kB)
0
SAMUEL Alexandre Feb 09, 2017 at 11:04 PM
0

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

time-dimension.png


time-dimension.png (95.7 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Feb 10, 2017 at 07:21 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

P.S. Hope you don't have any "special" members in TIME dimension (not month, quarter or year)

0
SAMUEL Alexandre Feb 10, 2017 at 10:34 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Vadim Kalinin Feb 10, 2017 at 10:44 AM
0

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 :)

Share
10 |10000 characters needed characters left characters exceeded