How to filter #NA (i.e. UNDEF) values in TI

[vladino]

Hi guys,
I'm trying to filter out #NA values in TI process - I don't want them to be copied from one cube to another one.

I have tried this:
IF (NVALUE <> UNDEF)
...

But this is not working. :-/

Could anyone help me with this issue?

BR
Vladino

[tomok]

There is no such thing as an #NA value in TM1. What exactly are you talking about?

[vladino]

I'm talking about division by zero etc. This gives you "NaN" values (which I translate as #NA).

[Duncan P]

I think you need ISUND.

[rmackenzie]

I'm talking about division by zero etc. This gives you "NaN" values (which I translate as #NA).

If you make sure you use \ rather than / in your rules it solves the division by zero problem you're experiencing in that the rule engine will give 0 rather than then cell error. These would then be skipped if you're suppressing zeros in the cube view you're processing.

There's another reason for cell errors when you've got a circular reference in your rules. I think the TI engine throws the error before you get a chance to code for the exception so, once again, fixing the rules is the way forward. If you've deliberately coded circular references then perhaps you want to rethink this as you'll get all sorts of problems with stack overflows and other bothersome issues - did you do that?

[vladino]

If you make sure you use \ rather than / in your rules it solves the division by zero problem you're experiencing in that the rule engine will give 0 rather than then cell error.

That's it! Thanks a lot!

[vladino]

Hmmm, still not solved... :-/

I have replaced slash with backslash to avoid dividing by zero. But I have discovered that TM1 uses #NA also for infinity, for example dividing by number which is almost zero (3e-323 in my case).

Does anyone know how to deal with this? I need to copy this cell from one cube to another one but I'm not able to avoid minor errors because of #NA (I'm using CellPutN).

Thanks a lot in advance!

[blackhawk]

Why is the number so small? Is it real or is it a rounding issue that should have been zero?

Well you could always use:

['Value'] = IF( ABS(['Other Value']) < .001, 0, ['Other Value'] );

FYI: #NA can also occur when you have a circular reference.

[vladino]

It's getting more and more weird...

I have traced the calculation up to the source and it should be zero (it is consolidated year value, months are linked to another cube with no data in months -> but months in target cube contain 4,9e-324, therefore yearly consolidated value also has non-zero value).

Why the hell TM1 changed the number and how to fix this?

[qml]

A general discussion on the subject of floating point number representation and precision and its side effects.

A potential solution.

[Duncan P]

If the exact value you are seeing is 4.94066e-324 then check whether you have (or have ever had) UNDEFVALS in the cube rule. This is the value TM1 uses to represent a null cell when UNDEFVALS is specified.

[EDIT] and BTW another interesting link