10 Replies Latest reply: Apr 12, 2011 9:18 AM by canmarroig

# If then....with negative percentage

Good morning, i've different %margin and i want to put a description in order to aggregate negative results, but i've made a mistake:

=if(%margin<-,50,'lower than -50%',if(%margin<-,30, 'between -50% and -30%',if(%margin<-.99, 'lower than 0', 'more than 1%')

can you help me?

Expression tell me that "," is wrong but i live in Italy and this is my metric unit.

Thank you

• ###### If then....with negative percentage

Hi

=if(%margin<-.50,'lower than -50%',
if(%margin<-.30, 'between -50% and -30%',
if(%margin<-.99, 'lower than 0', 'more than 1%')))

Regards

Anders

• ###### If then....with negative percentage

Expression is ok but every result tell me that are "more than 1%" also when numbers are negative

e

• ###### If then....with negative percentage

none of the conditions rendered true - must be something with your syntax, or with the number format?

• ###### If then....with negative percentage

Maybe, numbers are 25,17% not 25.17% but expression doesn't admit ","

• ###### If then....with negative percentage

in script i've settled this:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=','

isn't it enough?

• ###### SV:Re: If then....with negative percentage

Hi

You have to use '.' as decimal separator in chart formulas even if you have another setting for DecimalSep.

Also arrange your if-statement in the correct order, start check for lower than the lowest boundary, then for the next lowest etc.

Try this:

if(%margin<-.9,'Lower than -99%',if(%margin<-.5,'Lower than -50%',if(%margin<-.3,'Lower than -30%','Higher than -30%')))

That works for me (having DecimalSep=',')

• ###### SV:Re: If then....with negative percentage

Thank you very much!!!

• ###### SV:Re: If then....with negative percentage

A further question: is this possible to use this field (range) in a pivot to title a column? I'm trying to add this expression in a calculated field but tell me that is impossibile!

• ###### SV:Re: SV:Re: If then....with negative percentage

I suugest that you add the calculation of the margin range in the load script, where you load the %margin

...

if(%margin<-.9,'Lower than -99%',if(%margin<-.5,'Lower than -50%',if(%margin<-.3,'Lower than -30%','Higher than -30%'))) As [margin range]

...

then you can use the margin range as a dimension.

• ###### SV:Re: SV:Re: If then....with negative percentage

I must deep my knowledge in script. Thanks