Best Practice to change Negative numbers to Positive

[ExApplix]

Hi,

I need some advice to change the Negative numbers to Positive.

My data has got the Expense and Revenue numbers. All the Revenue numbers have Negative sign in the backend database and I want to see these numbers without the Negative sign in the TM1. What will be the best practice to do this?

Shall I use Attribute Formatting, Rules to change the signs or shall I change it in the Load Process (TI)?

If I will have to change the sign while loading the data into TM1 (using TI process) then I will have to change the signs again when offloading the data from TM1 to the backend database.

Please advice.

[Steve Rowe]

I like to load the data in the natural sign of the ledger this keeps the audit trail very clear and I can be sure that the trial balance =0. Typically I would load this into an element called Ledger in a dimension I often call "Book".

Then I have other Book elements like

Reporting Values
Reporting Values '000s
Reporting Values YTD
Reporting Values '000s YTD

I'd use rules to calculate these (except for the '000s one which are consolidations of the other with a weighting of 0.001)

Just my approach, I am sure others will have different flavours.
Cheers

[ExApplix]

Can you please explain your methodology? A sample rule will help.

If we can convert the sign using the Attribiute Formatting then it will be a lot easier, but not sure how can we do it.

[Marcus Scherer]

Hi,

I would convert it in the TI script.

If I will have to change the sign while loading the data into TM1 (using TI process) then I will have to change the signs again when offloading the data from TM1 to the backend database.

Yes, there's work in sight either for you or someone on the backend side. Both steps can be automated.

kind regards,

Marcus

[Steve Rowe]

Hi,

I'm not sure what you mean by Attibute formatting.

I'd use a rule

There are (at least) two choices

['Reporting Values']=N:['Ledger'] * CStr(Attrs('Account', !Account,'SignString'),1,0);

If you use a string attribute then you can write a rule against it, in your attribute cube you have something like the following

['SignString']=S:CStr ( If ( ElisAnc('Account','Revenue', !Account)=1,'-1','1') ,1,0);

This has the advantage that rule is totally automatic but the disadvantage that ElIsAnc is being constantly evaluated.
If you need to make your system the most efficent possible then you would have a second sign Attribute called 'Sign' you would then write a TI that copies SIgnString into Sign and you would need to set your business process up such that it is executed everytime the dimension is updated.

Also then change your rule to
['Reporting Values']=N:['Ledger'] *Attrn('Account', !Account,'SignString');

HTH

[ExApplix]

I am wodering if I can change the sign of Negative numbers using the 'TM1 Display Format' as shown here:

http://publib.boulder.ibm.com/infocente ... ues_N701AF

or by using 'Display Format Attributes', as shown here:

http://publib.boulder.ibm.com/infocente ... 1%74%22%20

Please suggest me if this possible through the Display Formats.

[Steve Rowe]

There is nothing there to suggest it would be possible to do that, have you tried? What format strings have you tested?
Even if it were possible I would not suggest it since your Cube viewer / TM1 Web environment would end up displaying different values to Excel slice for the same cells in a cube.
Cheers