Issue in Calculating YTD Values to PERIODIC In YTD Model

[Former Member]

Issue in Calculating YTD Values to PERIODIC In YTD Model 

My model name is a REPORTING which is a YTD model & inputted data in YTD as Below

When I run the report (shown below) with measures as PERIODIC system is displaying months 1/4/7 system is taking YTD values and showing them as periodic values.

As per standard behaviour by default the YTD data must be shown in PERIODIC as

PERIODIC Value = {Present Month YTD Value} – {Previous Month YTD Value}

But in my report I got below values in periodic

So if see above for every quarter first month PERIODIC value is posting same as YTD value

But it is not repeating for the rest of the months in every quarter so please rectify the issue & locate where the issue…is.

My FORMULA for measures for PERIODIC is as below

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%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 ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN"), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3 

And also let me know how the “LAG()” searches & on what bases it will identify the previous month

[former_member186338 · Accepted solution]

Hi Aravind,

Please upgrade to the LATEST SP your core BPC and BW!

Vadim

P.S. About LAG and other MDX functions you can read on the Microsoft site: Lag (MDX)

[Former Member · Accepted solution]

Hi Vadim,

        Thank you for your suggestion .

           Our system is already up to date & it is BPC 10.1 & BW 7.40....Patches up to date.

                 But issue is LAG() is not referring the correct month it is repeating the same month so if you could help me in referring the previous month in the measures code instead of LAG() it will be a great favor to me..

[former_member186338 · Accepted solution]

Hi Aravind,

You measure formula is absolutely correct! Just for reference - same formula from IFS kit for YTD model:

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%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 ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN"), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

Please provide SP levels for BPC and BW!

Also, please provide screenshot of administration screen of TIME dimension!

Vadim

[Former Member · Accepted solution]

My BPC SP 21 release 810 level 004

    BW release 740 level 009

Please find my hierarchy table attached below

[former_member186338 · Accepted solution]

Try the following:

1. Create a new copy of Environment Shell and test!

2. Create a new TIME dimension and a new model with this TIME dimension in the existing Environment and test.

Vadim

[Former Member · Accepted solution]

I agree with you vadim this issue doesn't occur for the default "TIME" dimension that is in the environment shell.

But the issue raises when we modified the TIME dimension master data.

1) The main issue is in the "Measures" Formula at the end the LAG() function is not referring the correct "NODEID" of the "TIME" master data .

So i need a solution to overcome this.

[former_member186338 · Accepted solution]

"But the issue raises when we modified the TIME dimension master data." - how you modified it?

Please provide your BW and BPC SP levels!

Vadim

[Former Member · Accepted solution]

My BW SP level is 10 (740)

BPC SP level is 21 (810)

[former_member186338 · Accepted solution]

"My BW SP level is 10 (740)" - move to SP11

"BPC SP level is 21 (810)" - impossible - SP6 is the latest

[Former Member · Accepted solution]

Yep sorry BPC SP 04

               BW   SP 09

Are you sure about that if we upgrade to NEXT level of SP this "ISSUE" resolve....!

[former_member186338 · Accepted solution]

"Are you sure about that if we upgrade to NEXT level of SP this "ISSUE" resolve....!" I am not 100% sure without detailed review of your system It's your job to perform a test migration and to check results...

Vadim

[Former Member · Accepted solution]

Ok Vadim i agree with you, We are trying to do that & Thank you...

[Former Member · Accepted solution]

Issue resolved after upgrading BW-SP From 09 to 11........

        Thank you for the suggestion Mr.Vadim. 

[former_member5472]

Hi,

Please check this note

2136030 - Incorrect periodic values for YTD model - BPC NW 10.0/10.1

Prat

[Former Member]

Hi Pratyush,

                    Thank you for the note but that was raised by our team only we already installed that note but, that doesn't reflected any impact.

Still the issue remains same....................!