Roll up dimension time

[Former Member]

Dear all,

I'm just trying to resolve a problem to show data in a Yearly period for Balancesheet accounts. The data has been entered in Q2.2014 and when i'm trying to create a report in Yearly period theres no results. I saw a note that for Balancesheet accounts it need to show the results in for example December 2014. How can we resolve this problem because the client in the latest project need to see the results in a Yearly basis when they input data in a querterly basis.

Thank you in advance

George

[Former Member]

Hi George,

Since you are working with BalaceSheet information, the configuration has to be done in custom measure (SE38 - UJA_MAINTAIN_MEASURE_FORMULA). You should configure this so the YEARLY values can show the last period (Ex. 2014 shows 2014.12 data).

Ex.

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/FWP7U92")="F",

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/CALC")="Y",

([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),

([MEASURES].[/CPMB/SDATA])),

In this example I used an account property to say witch accounts should consider the value of december, instead of summing it. But for Balance accounts you can use the Account Type.

Hope it helps.

Adriano Frossard

[former_member186338]

Hi Adriano,

What are you talking about is a standard YTD measure approach - CLOSINGPERIOD returns last sibling at a specified level. And if DEC is not loaded (zero) - it will show Zero as Year total.

Vadim

[Former Member]

Ah so... In this case it woud be necessary to replicate the data from the previous "filled" period to December, isnt it possible?

[former_member186338]

Please read my previous answers!

[former_member186338]

Hi George,

It's not an issue it's a definition of YTD figures: year result = DEC result.

You may copy the last month figures (balance sheet accounts) to all empty months including DEC

Vadim

[former_member186338]

Just Imagine the following:

          OCT         NOV          DEC    TOTAL

AC1        5    <empty>   <empty>         ?

AC2        7                9   <empty>         ?

Figures are loaded for OCT and NOV, but AC1 in NOV is zero resulting in empty record. What do you want to have in TOTAL for AC1 and AC2?

Vadim

[Former Member]

Hi all,

Thank you for your answer, the problem is that the client a stock listed company with about 200 bilion revenues , need to report the balancesheet in quarter period, this means q1, q1,q3,q4 and the yearly Balancesheet. Also they report in semesters , which mean q1 and q2 first semester and q3 and q4 the last one. Also according to the behavior of BPC we cant post in parents but only in childrens, which mean that the customer will be load the data in the last month of the parent period, even there are quarterly or semester or yearly. So if we upload the data in the last month of q2 (June) in total year we need also to have the data in the report in you example

Q2 Period for 2014

          April         May          June    2014.TOTAL

AC1 <empty>  <empty>       10         10

AC2 <empty>  <empty>       10         10

[former_member186338]

Please look on my previous answer! The system has no idea what is the last uploaded month Try to answer my question in my prev. post and you will understand why YTD is presented Year results this way.

Vadim

[Former Member]

Hi Vadim,

Thank you for your answer, yes its fully understud about this way. My problem is how can we avoid this and to transfer the data of a month in the Yearly Period which mean in December.

Maybe with a script or a business rule?

Thank you George

[former_member186338]

Simple script can do the job:

// Assuming that %TIME_SET% contains the last uploaded month!

*SELECT(%Y%,"[YEAR]",TIME,"[ID]=%TIME_SET%") // get year

*SELECT(%BS%,"[ID]",ACCOUNT,"[ACCTYPE]=AST OR [ACCTYPE]=LEQ")

*XDIM_MEMBERSET ACCOUNT=%BS% // only balance sheet accounts

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNT

*IS * // scoped BS accounts

*REC(EXPRESSION=%VALUE%,TIME=%Y%.DEC) //or %Y%.12

*ENDWHEN

Script is for BPC 10, for 7.5 you have to inverse sign for LEQ

Vadim

[Former Member]

Dear Vadim,

I use this script , but with no success.

What should i do after the validation of the script.

Thank you in advance

George

[former_member186338]

You have to create in Excel client new DM package with the chain DEFAULT_FORMULAS_LOGIC and edit the script of the package (change name of LGF file).

Please read some documents in How-To section.

Vadim

[Former Member]

Hi George,

is Q2.2014 a base member in your time dimension? or what do you mean by data has been entered in Q2.2014.

Also check in your report that you have ticked calculate parent node setting

Andy