Round Algorithm

[Aysun]

Hi all,
I have a problem with round algorithm.For example,I have consolidated items and consolidated items have leaf levels.

Account Balance Balance_round Difference Balance_Round1 Balance_Round_2 Flag
020 31.8 33
0201 10.6 11 0.4 10.2 10 1
0202 10.6 11 0.4 10.2 10 1
0203 10.6 11 0.4 10.2 10 1
021
0211 10.4 10 -0.4 10.8 11 1
0212 10.4 10 -0.4 10.8 11 1
0213 10.4 10 -0.4 10.8 11 1
When I use round function;all 10.6 round 11 and and 31.8 round 32.So,sum of leaf levels not equal the consolidated item.So I wrote a rule;If the level is leaf then balance-difference.10.6-0.4 =10.2 when I round these it return 10.So I want to apply this rule only one leaf level in consolidated item.How can I write this condition?When I create a flag this situation ,it writes flag(1) all cells which affected by rule naturally.How I round only one leaf level in consolidated item?

Thanks in advance.

[rozef]

Hi Aysun,

to make sure your round difference at consolidate level will be reported on 1 leaf you can use ELPAR and ELCOMP function and compare your current element:

Code: Select all

[...]=N:IF( !MyDim @= ELCOMP( ELPAR( 'MyDim' , !MyDim , 1 ) , 1 @= !MyDim , then put difference , continue ) 

This make sure you only have difference on first children of level 1 parent elements.

But I am not sure it is a good aproach to do this. Why one partial element will be in charge of accumulating all round difference?
I guess flags elements (as you already have some) for each specific hierarchies would be a better way to know what come from round difference or not.
You could also divize the difference by the number of children and repart on all of them with ELCOMPN function.

[Steve Rowe]

Hi,

Any particular reason you are going to such length? Rounding ought to be a formatting issue best dealt with in the reporting layer rather than manipulation of the base data.

All the reporting environments from the cube viewer upwards allow you to set a format so I don't really understand why you want to go down the road you are...

Cheers

[ioscat]

Безымянный.png (6.06 KiB) Viewed 7024 times

updated 29.11
column C has function cut only at leaf elements.

[Steve Rowe]

Yes understood but which column, other than A has the right answer?

IMO B is more right than C since each cell contains the most correct information.

In C you have forced the report to show the incorrect number in order to make the report "correct".
Any consumer of finance data ought to be able to understand that rounding produces reports that at face value do not add up.

If you are going down this path so you can get to C, this seems wrong to me.

[Aysun]

Hi all,

Parent level value is constant .For example;31.8 have to be rounded 32.Mean that sum of child level value not equal parent value.
In my case,I have to down or up round one of the leaf level to equalize parent level value.

rozef your suggestion ;


['a']=IF(ELCOMP('x', ELPAR( 'x' , !x, 1) ,1)@=!x, 1 , continue );

So,It means that,first child level in consolidate level when I write 1.So this situation can be occured in 5.leaf level.

10.6->5.level
10.6->6. level
10.6->7. level

So I want to set ['a'] when the first cell that condition is occured.

[rozef]

Depending on your conditions you can reach any level or child:

['a']=IF(ELCOMP('x', ELPAR( 'x' , !x, 1) ,1)@=!x, 1 , continue ); => put 1 on first child of all parents in dimension x
['a']=IF(ELCOMP('x', ELPAR( 'x' , !x, 1) ,1)@=!x, 2 , continue ); => put 1 on first second of all parents in dimension x


['a']=N:IF(ELCOMP('x', ELPAR( 'x' , !x, 1) ,1)@=!x, 1 , continue ); => put 1 on first child of level 1 parents in dimension x
['a']=C:IF(ELCOMP('x', ELPAR( 'x' , !x, 1) ,1)@=!x AND ELLEV( 'x' , !x = 1 ) , 1 , continue ); => put 1 on first child of level 2 parents in dimension x


etc.

[tomok]

I feel your pain on the rounding issue. I encountered this when I first started using TM1 back in 1995. My employer back then bought TM1 to do financial consolidations and as a bank, we were accustomed to preparing all our financial reports rounded to thousands. However, the requirement was that all financial statements had to foot, meaning we couldn't just round in the formatting because often times the numbers wouldn't add up. Back then my solution was to pick a specific child of each parent to hold the rounding difference, a plug if you will, so that the financial statements would foot. Then I hard-coded these elements in the rules to make these children equal the difference between the rounded parent and the sum of all the other rounded siblings. This works fine but it's rather ugly and may require maintenance any time the financial statement line items change. This may be your best option if you are relatively unsophisticated with TM1.

After I became more experienced with TM1 I devised a more dynamic solution to the rounding problem. The scheme involves adding a new dimension to the cube, call it Rounding, with a leaf element called Raw (where the raw data is stored) and then elements to hold the numbers rounded, as well as elements to hold the adjustments to make sure everything foots. You need multiple steps in the tree, depending on how many levels your account structure contains. It would look something like this picture:


The next thing you need is attributes on the Account dimension. One for the account number of the element which will hold the rounding adjustments (the attribute will be populated for each parent in the account hierarchy) and then an attribute to hold the name of the parent (will be populated for the accounts designed as holding a rounding adjustment). You will need a parent attribute for each level in the hierarchy like Level_1_Parent, Level_2_Parent, Level_3_Parent, etc.
Then the rules look like this. The first rule rounds the raw balances to 1000.

Code: Select all

['Rounding_Level_0']=N:ROUNDP(['Raw']\1000,0);

Then you have a rule for each level in the tree beyond 1:

Code: Select all

['Rounding_Adj_Level_1']=N:
IF(!Account@=ATTRS('Account',ATTRS('Account',!Account,'Level_1_Parent'),'Rounding_Account'),
	ROUNDP(DB('Income_Statement',!Version,'Raw',!Org,ATTRS('Account',!Account,'Level_1_Parent'),!Period,!Measure)\1000,0)-
	DB('Income_Statement',!Version,'Rounding_Level_0',!Org,ATTRS('Account',!Account,'Level_1_Parent'),!Period,!Measure),
	0);

You would repeat this rule until you get to the top level in the account hierarchy. Then the rule is (assuming hierarchy has 5 levels and top level element is “Net Income”):

Code: Select all

['Rounding_Adj_Final']=N:
IF(!Account@=ATTRS('Account',ATTRS('Account',!Accounts,'Level_5_Parent'),'Rounding_Account'),
	DB('Income_Statement',!Version,'Rounding_Level_4',!Org,'Net Income (Loss)',!Periods,!Measures)-
	['Net Income', ‘Rounding_level_0’],
	0);

Lastly, you need to feed:

Code: Select all

['Raw']=>['Round_is':'Rounding_Level_0'];
['Raw']=>DB(IF(ATTRS('Account',!Account,'Level_1_Parent')@='','','Income_Statement'),!Version,'Rounding_Adj_Level_1',!Org,!Account,!Period,!Measure);
['Raw']=>DB(IF(ATTRS('Account',!Account,'Level_2_Parent')@='','','Income_Statement'),!Version,'Rounding_Adj_Level_2',!Org,!Account,!Period,!Measure);
['Raw']=>DB(IF(ATTRS('Account',!Account,'Level_3_Parent')@='','','Income_Statement'),!Version,'Rounding_Adj_Level_3',!Org,!Account,!Period,!Measure);
['Raw']=>DB(IF(ATTRS('Account',!Account,'Level_4_Parent')@='','','Income_Statement'),!Version,'Rounding_Adj_Level_4',!Org,!Account,!Period,!Measure);
['Raw']=>DB(IF(ATTRS('Account',!Account,'Level_5_Parent')@='','','Income_Statement'),!Version,'Rounding_Adj_Final',!Org,!Account,!Period,!Measure);

The end result of all of this is that now your financial statements will foot at the whole dollar level (Raw) and when rounded (Rounding_Final).

[ioscat]

hi, tomok.
In your post there is no picture. Could you please illustrate your idea some obvious example? maybe on picture from my post