Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
canmarroig
Partner - Creator
Partner - Creator

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



1 Solution

Accepted Solutions
gandalfgray
Specialist II
Specialist II

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=',')

View solution in original post

10 Replies
Not applicable

Hi

Please try

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

Regards

Anders

canmarroig
Partner - Creator
Partner - Creator
Author

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

e

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

canmarroig
Partner - Creator
Partner - Creator
Author

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

canmarroig
Partner - Creator
Partner - Creator
Author

in script i've settled this:

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

isn't it enough?

gandalfgray
Specialist II
Specialist II

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=',')

canmarroig
Partner - Creator
Partner - Creator
Author

Thank you very much!!!

canmarroig
Partner - Creator
Partner - Creator
Author

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!

gandalfgray
Specialist II
Specialist II

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

Load

...

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.