TM1Web and Perspectives diff. on double quoted numbers

[John Zhang]

There is a discrepancy observed on Excel formula behaviors in TM1Web and Perspectives regarding a number wrapped in double quote marks.

We tried to use IF formula to compare whether a simple number (say, a number 5) equals to a specific value. We found that in Excel the IF statement will return FALSE when the Type of Data (i.e. a "number" or a "text") is different. Whilst in TM1Web it will return TRUE even if we compare a number (say, 5) with a text (i.e. “5”). Some examples are given in below table.

Has any one else ever seen this behavior before? Advice on how to gracefully write Excel formulas for TM1Web to avoid this is also appreciated.

TM1 version: 10.1.1
Excel: 2003 or 2007

Formula	Result in Excel	Result in TM1Web
=IF(9=9, "TRUE", "FALSE")	TRUE	TRUE
=IF(9="9", "TRUE", "FALSE")	FALSE	TRUE
=IF(9.01=9.01, "TRUE", "FALSE")	TRUE	TRUE
=IF(9.01="9.01", "TRUE", "FALSE")	FALSE	TRUE

[rmackenzie]

This might work:

Code: Select all

=IF(TEXT(A1,"@")=TEXT(B1,"@"),TRUE,FALSE)

That way its apples and apples. Note there's no need to quote the TRUE and FALSE outcomes otherwise you will be casting strings back to booleans.

[blackhawk]

Hmmm....that is interesting...have you tried this in 10.2.2 yet?

[John Zhang]

This might work:

Code: Select all

=IF(TEXT(A1,"@")=TEXT(B1,"@"),TRUE,FALSE)

That way its apples and apples. Note there's no need to quote the TRUE and FALSE outcomes otherwise you will be casting strings back to booleans.

Thanks Robin for the good code suggestion. What does the @ symbol do as placed in format parameter for TEXT function?

And yes, you are right, no need to add quote for TRUE and FALSE. That was just kept there when I replaced the original output texts.

[John Zhang]

Hmmm....that is interesting...have you tried this in 10.2.2 yet?

Nope.... not yet. Anyone tried it please let us know.

[rmackenzie]

What does the @ symbol do as placed in format parameter for TEXT function?.

It's part of the syntax for custom number formats - in this case, it converts a number to a string. It's a bit like sticking an ' in front a number. For your problem, it is just saying compare text with text, not numbers with text which won't work, i.e. this is FALSE:

Code: Select all

="1"=1

But this is TRUE:

Code: Select all

=TEXT(1,"@")=TEXT("1","@")

Does this solve the TM1 Web problem? I don't know if TM1 Web will interpret all the different types of custom number formats that can be set in Excel itself.