Formatting issue

[Nick_Blommaerts]

Hello all,

I'm quite new to TM1, perhaps the following may be a rather stupid question, but here goes..

Basically, I am building a form to plan the volume per month, per promotion (action). In order to get this done, I've built a cube with a range of dimensions related to the customers/products/time/version/etc. On top of that, I've created one dimension promo and a measure dimension promo_m.

The promo dimension consists of 15 promo elements with a total_promo consolidated element above. The dimension promo_m contains an element named action, in which I would like to store the name of said promotion, and an element volume, in which the data for the volumes will be stored. The element action is defined as a string, the element volume as a number.

The problem I'm experiencing now is something I haven't run into in my rather short TM1-career, but I can't seem to find much information about it in the documentation, hence this topic. Every action at some point in time has a certain name to it, which is to be stored in the action element (reason I don't store this as an attribute of the promo dimension is to retain historical data). So far, so good, unfortunately certain names I give to actions seem to cause problems.

The image below shows our problem at hand, basically whenever I try to give a certain action a name like "70%", it changes it to 0,7. To prevent that from happening, and since %-values are not the only possible names for an action, I figured that changing the formatting of the cells in the format area to "Text" would sort everything out as it should, but strangely enough that is not the case.



Whenever I try to fill in a name of an action containing only numbers and a % (e.g. 60%), I get following error message:



Please note that if I do the same thing in a cube view and then refresh my Excel-form, it does work perfectly fine, the first cell for example in the first image has been done like that.

A couple things I'm trying to understand:
- Is this TM1-related or an Excel-problem.
- Could it be that, somehow, TM1 has a problem with the combination of a number and the % symbol when the element has been set to be a string.

Thanks in advance.

[lotsaram]

The problem is with Excel rather than TM1 as Excel is trying to be "smart" by "helping" you by automatically converting strings it recognizes as numbers into numeric values for you (you would have the same issue with dates also).

The solution is simple, just preface your string with a single quote ( ' ). Excel will then be forced to see the contents of the cell as a string rather than converting to a value for you. (You can't change the Excel cell formatting to "text" as this will switch all formulas off and your DBRWs then will not work.)

[Nick_Blommaerts]

Thanks for your swift reply, this more or less confirms what I expected, Excel is the culprit. It's a shame it cannot be evaded somehow though.