on 06-25-2015 9:57 AM
Hi All,
I am Having Below issue:
Scenario: Build up report on top of YTD model which shows data for both YTD and PER measures for Income statement.
Issue: In this report YTD and PER both are giving same values.
It should convert data from YTD to per and PER column should get updated.
What I should get is:
JAN FEB MAR APR
YTD PER YTD PER YTD PER YTD PER
GL1 100 100 200 100 100 -100 300 400
What I am getting is:
Hi,
I believe this note will resolve your issue
2136030 - Incorrect periodic values for YTD model - BPC NW 10.0/10.1
Prat
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This issue is specific to few environments only. In other environments the periodic values are being computed correctly. After further analysis, we found out that the error is on account of LAG formula used in measures formula.
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
For Apr, July and Oct months, the LAG function is not referring to the previous month and for other months the LAG function is referring to correct previous month (as mentioned, this error is pertaining to few environments). We have custom properties in TIME dimension in this environment. Is this error on account of this.
Hello,
You said all periodic values are correct except for Apr, July and Oct. That is consistent with my analysis of your data but I couldn’t tell why. Based on your measure formula you are trying to return the prior period for time, [%TIME%].LAG(1). So I suggest checking your Time dimension and confirming if the prior periods for April, July, Oct are what you expect.
Regards,
Leila
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vadim,
I checked them, this report is having only INC/EXP type accounts, Profit and loss report.
Its build up on YTD model and we are getting YTD ACTUAL data from source into this model.
Is it like we should not transfer P & L accounts data into YTD model??
by default from source we are getting YTD data only for both BS and IS accounts.
Regards,
Priyanka
Hello,
With YTD storage all values entered for P&N accounts are considered as cumulative of all values from the start of the fiscal year. So when reporting with periodic measure on YTD storage, the value for each period is calculated by subtracting the last period YTD value from the current period YTD values. With that in mind, the periodic values that you are showing for Jul, Aug, Sept and November are correct.
To put it simpler, your August, September and November periodic values are correct. However, in June and October the values are showing the same as periodic which can happen if you are using QTD instead of YTD measure. Please check the measure in your report and confirm it is YTD. Other than that the values seem to be correct.
Best Regards,
Leila
User | Count |
---|---|
14 | |
4 | |
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.