TM1 Forum

Question isn't crucial but simply it is interesting.

I have Items of PL in rows in report form. Users put numbers without sign in their forms.
I want to show numbers in report form with signs. Of course I can multiply on 1 or -1 in rules
but it would be interesting is it possible to format number (positive or negative) based on attribute 1 or -1 which put for every Items.

I assume you mean in an active form? If so then easy, you don't even need conditional formatting (which BTW does finally support number format as of Excel 2010).

You just need to change the format ID column formula to be based off your formatting attribute rather than the default dimension level different to subset root formula. Then for example DR accounts that you want to display as negative values just set a cell number format something like -#,##0;#,##0;"-"
This will effectively just use excel number formatting to "flip" the display of numbers to *-1 so a value of 100 will display in the cell as "-100"

Bet you didn't think it was that easy?

Well, another, arguably 'cleaner' way worth considering would be to create parent consolidations for your measures with appropriate positive or negative weighting. Users would use the leaf measures for input, as they do now, but reports would reference the parent elements and as a result show numbers with the correct signage. The advantage is that this would then work consistently in all interfaces, without any need for funky formatting tricks that would only work in Excel.

qml wrote:Well, another, arguably 'cleaner' way worth considering would be to create parent consolidations for your measures with appropriate positive or negative weighting. Users would use the leaf measures for input, as they do now, but reports would reference the parent elements and as a result show numbers with the correct signage. The advantage is that this would then work consistently in all interfaces, without any need for funky formatting tricks that would only work in Excel.

I 100% agree. But if the "measures" dimension happens to be the chart of accounts then having an artificial C element with -1 weighting for say all expense and all liability accounts may not be very "clean" either and could create a lot of clutter depending on the UI and maintenance.

It seems I can put simply format
-#.##
for Items which have negative sign.
And it is enough

Excel number formats are of the form
<positive number format>;<negative number format>;<zero value format>;<text value format>
If the semi-colon separator is not used then the supplied number format is used for all cases
Thus if you use a custom number format -#.## then a value of 100 will display as "-100." but a value of -100 will display as "--100." with a double negative sign. I don't think that would be what you want. Although most accounts have a defined natural DR or CR balance type it can certainly happen then an account can carry an opposing balance. I would think you would want to cover off this possibility...

lotsaram wrote:Thus if you use a custom number format -#.## then a value of 100 will display as "-100." but a value of -100 will display as "--100." with a double negative sign. I don't think that would be what you want.

I see. But in my case I know that all values in the report form are positive (because users input them as positive), so I want to transform their showing from positive to negative in some accounts.
But of course if values could be negative or positive it isn't possible to apply format -#.##. Apparently I should apply -#.##;#,## - to transform positive to negative and do nothing with negative (I think so - I haven't check yet this format -#.##;#,##)