very very small aggregation amount

[macsir]

Hi,

Whenever there is a very very small aggregation amount (e.g. 0.0000000043) in the cube view, TM1 can't suppress it at all in the activeform report. I mean, for business, they really don't care about this small amount and want to take it out from the report.
I have tried rule-driven amount based on roundp function to make that aggregation to be real zero but since it needs feeders from that small aggregation amount, TM1 still can't suppress it in the cube view.

Example is following, it is real 0 in new amount but very small amount in the amount. Anyone has better idea?

[BrianL]

Take a look at https://www.ibm.com/developerworks/comm ... ng%20Point

There's some useful information about how TM1 uses floating point numbers which are inexact by definition and some suggestions to help deal with it. Including an otherwise poorly documented config parameter MagnitudeDifferenceToBeZero.

[macsir]

Take a look at https://www.ibm.com/developerworks/comm ... ng%20Point

There's some useful information about how TM1 uses floating point numbers which are inexact by definition and some suggestions to help deal with it. Including an otherwise poorly documented config parameter MagnitudeDifferenceToBeZero.

Thanks for the info. I have had a read of that. But the MagnitudeDifferenceToBeZero config parameter only affects the operation of the 'safe division' operator in TI processes and cube rules, which is not my case. Thanks anyway.

[paulsimon]

Hi macsir

Can I check that I understood you correctly?

Presumably you want zero suppression to work and it doesn't because there is a very small amount.

You tried a rule calculated value using roundp but that still didn't work.

Did you apply the rule at all levels?

Did you only show the New Amount in the view (excluding Amount)? However, I think you know enough to know that zero suppression will apply across all columns.

I can suggest two possible things

a) Use an IF test with ABS ['New Amount'] = IF( ABS( ['Amount'] ) < 0.01 , 0, ['Amount'] ) ;

However, the downside of this is that you would have to feed New Amount, and that will double the cube size, just to get rid of the odd small value.

b) The other approach might be something like an MDX FILTER on the value applied to the dimension on the rows. You can either do this as a dynamic subset referenced in the TM1RPTROW in the Active Form, or use a Base Subset and put the MDX into the TM1RptRow.

c) If the report is delivered in Excel, apply a Data Filter to take out rows they don't want.

Regards

Paul Simon

[macsir]

Hi macsir

Can I check that I understood you correctly?

Presumably you want zero suppression to work and it doesn't because there is a very small amount.

You tried a rule calculated value using roundp but that still didn't work.

Did you apply the rule at all levels?

Did you only show the New Amount in the view (excluding Amount)? However, I think you know enough to know that zero suppression will apply across all columns.

I can suggest two possible things

a) Use an IF test with ABS ['New Amount'] = IF( ABS( ['Amount'] ) < 0.01 , 0, ['Amount'] ) ;

However, the downside of this is that you would have to feed New Amount, and that will double the cube size, just to get rid of the odd small value.

b) The other approach might be something like an MDX FILTER on the value applied to the dimension on the rows. You can either do this as a dynamic subset referenced in the TM1RPTROW in the Active Form, or use a Base Subset and put the MDX into the TM1RptRow.

c) If the report is delivered in Excel, apply a Data Filter to take out rows they don't want.

Regards

Paul Simon

Hi, Paul

Thanks for the advice. I have applied the rule to the C level only coz it is just for consolidation. I know how exactly the suppression works.
a) Actually, for this issue, the rule won't work, for neither ABS nor Roundp function. You can use the function to make that consolidated cell a real ZERO but since you have to use feeders for the rule, TM1 will never suppress that cell because all child feeders under that very very small amount are not zero anyway after aggregation and this is the only way to feed that new amount.

2015-11-10 14_29_22-Rules Tracer_.jpg (85.87 KiB) Viewed 8483 times

b) I have implemented this method in the report, hopefully, the user can accept it.
c) This is the last resort and I won't easily go there for the moment.

[macsir]

Anyone has further idea?

[paulsimon]

Hi macsir

Interesting one. I would not have expected the zero suppression to still show the consols.

At least the MDX Filter approach I suggested is one viable method, but I agree, it would be better to centralise this in the cube.

I also tried routing the value across to a text value with FeedStrings. The issue there is that when zero suppressed the consols won't show.

Another possibility might be to route all the consol values out to another cube that has a flattened hierarchy, ie the consols in the original dimension are base level. When pulling in the data to the flat cube, apply the IF( ABS( val ) < 0.01 etc there. That would probably work but then you have two cubes to manage eg report from the flat cube but show the hierarchy from the original cube.

It might be possible to pull the data back into the original cube but that would need feeding, and I am guessing that you have something like two consol legs where one branch is say 100m and the other is -100m and 1/1000th of a penny, which gives a not quite zero value on the consol, That would mean that even if you pull the values back in, you still get the same issue.

I am fortunate that my users have generally just accepted that it will show the odd zero row.

Regards

Paul Simon

[macsir]

Thanks for the sharing, Paul.

Yes, I have this idea too but it is not nice way to deal with this issue.
I am just wondering if TM1 has a better way to overcome it coz it might be very common especially for the data coming from any finance system .

[tomok]

If it were me and this was something that just absolutely had to be done I would create a new measure called "Rounded" and then make the rule (assuming you still want the pennies, if not you could round them off too):

Code: Select all

['Rounded']=N:ROUNDP(['Amount'],2);

You have to apply it at the leaf level for it to work properly with zero suppression. I know this works because I have implemented this approach before. Not ideal because it can greatly increase the size of your cube but if you absolutely want to get rid of the small amounts it's the only way I know to do it.

[David Usherwood]

You could use TI instead of a rule to freeze the content, rounding as you go.

[lotsaram]

You say the very small amounts come from a source system? Sounds strange since most transaction systems wouldn't post such values. But if that is the case then why not do a check during the TI import and round verry small values to zero and remove the problem at the root?

If the values are that small then this will have no effect on reconciling the total to the source system anyway.

[macsir]

Hi, All

Thanks for the advice firstly. I have exported all the detailed values into a spreadsheet (and I have checked it also in the flat file format) and for those values from source system, they do have max decimal places up to 2. So the issue is not with source system but is in the process of aggregation of TM1 (which is similar in excel sum function). Please see the snapshot below. The first column are the exported values from TM1.

2015-11-13 10_58_36-Microsoft Excel - rounding.xlsx.jpg (97.62 KiB) Viewed 8366 times

The issue starts at row 19, which gives me lots of "9" as you can see and the length of that value confirms it as well. And it happens again at row 22 towards the end, which is showing very very small number at row 35.

[macsir]

If it were me and this was something that just absolutely had to be done I would create a new measure called "Rounded" and then make the rule (assuming you still want the pennies, if not you could round them off too):

Code: Select all

['Rounded']=N:ROUNDP(['Amount'],2);

You have to apply it at the leaf level for it to work properly with zero suppression. I know this works because I have implemented this approach before. Not ideal because it can greatly increase the size of your cube but if you absolutely want to get rid of the small amounts it's the only way I know to do it.

I tried this and applied this rule at N level only as well but no luck. I traced that calculation of the very very small amount. It is not a really zero. And you can see those long tails of consolidated parents, which cause this issue in the final aggregation.

[paulsimon]

Hi macsir

One idea I looked at to try to resolve this was to multiply all values on entry by a large number eg 1000, etc, with the aim that eliminating the decimal places might then prevent the floating point arithmetic.

However, this still did not work in terms of zero suppression.

Even when I just use a consolidation with one element in each leg with a + whole number in one and a - whole number in another, the TM1 View will still not suppress zeros once the consolidation becomes 0 as they two numbers cancel each other out. The upper level consolidations are always shown.

Therefore I think that the underlying issue is due, not necessarily to the small numbers, but due to feeding. My guess is that it is the feeder at the base level which prompts TM1 to show the consolidations in a zero suppressed view.

Personally I think that this is actually a good feature. I have more users who are concerned about possible big variances being hidden at lower levels. This feature at least lets someone see at the top level whether a measure has a value so they they can drill down.

However, for your situation, this feature is causing your problems.

It probably won't work in a cross dimensional view, but if in the view you are only concerned about one dimension, a possible way around it might be to use security, so that the element is given NONE access if the absolute value is below a threshold, so the user cannot see the element at all. However, that is no good if the user can vary eg the title elements in the view.

Regards

Paul Simon

[macsir]

Hi, Paul

Thank you very much for your detailed post. Yes, I have similar thought as you except for that security consideration.
For now, the only possible best solution is to use that MagnitudeDifferenceToBeZero parameter. But it will impact all numbers in current TM1 server and it is not working at all, which I posted this problem in another thread. Not sure how to make MagnitudeDifferenceToBeZero work although I have followed right instructions.