Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Number formatting issue

Hi ALL,

I have a 2 fields as follow:

Capture.PNG

I want to transform them to percentage %:

so, this is how I proceed:

but I still have the same result:

num(num#( [Notional_Variation],'#.##') ,'# ##0,00%') AS [Notional_Variation],

num(num#( [Threshold_Variation],'#.##') ,'# ##0,00%') AS [Threshold_Variation],

Capture.PNG

What should I do?

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Number formatting issue

Omar, one more thought. I see that your input and output fields have the same name. Can you try renaming the output field? If I remember correctly this could be a side effect of Qlik's dual data type. The value is displayed in that format in which it was loaded for the first time. So if you are just "reformating" the same field, this might not work as expected.

12 Replies
brunobertels
Valued Contributor II

Re: Number formatting issue

Hi

In your straight table did you chek the output format ?

Capture.PNG

Re: Number formatting issue

How does your environmental variable look like? Can you share the script for that?

OmarBenSalem
Esteemed Contributor

Re: Number formatting issue

No, I want to do it in the script; because I'll be directly sending this table into a web service that's directly send emails ...

one stupide Idea to format and show by %:

left( [Notional_Variation]*100 ,4) &'%.' AS [Notional_Variation],

left([Threshold_Variation]*100 ,4)& '%.' AS [Threshold_Variation],


result:

Capture.PNG

OmarBenSalem
Esteemed Contributor

Re: Number formatting issue

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep='.';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='fr-FR';

SET CreateSearchIndexOnReload=1;

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';

Re: Number formatting issue

Your decimal separator is . and not ,, right?

try this may be

num(num#( [Notional_Variation],'#.##') ,'# ##0.00%') AS [Notional_Variation],

num(num#( [Threshold_Variation],'#.##') ,'# ##0.00%') AS [Threshold_Variation],

OmarBenSalem
Esteemed Contributor

Re: Number formatting issue

that won't do it Sunny, since every time Qlik finds the %, he will automatically divide the value by 100, but won't show the % sign:

Capture.PNG

that's why, I added . next to %, to force qlik to show the % and then I 'll have email like follow:

Capture.PNG

Luminary
Luminary

Re: Number formatting issue

Hi Omar,

have you tried specifying decimal and thousands separatos in Num()?

num(num#( [Notional_Variation],'#.##') ,'# ##0,00%', ',', ' ') AS [Notional_Variation]
Juraj

OmarBenSalem
Esteemed Contributor

Re: Number formatting issue

Yes Juraj,

That was one of my first tries:

num(num#( [Notional_Variation],'#.##') ,'# ##0,00%', '.', ' ') AS [Notional_Variation]

Capture.PNG

Luminary
Luminary

Re: Number formatting issue

Omar, one more thought. I see that your input and output fields have the same name. Can you try renaming the output field? If I remember correctly this could be a side effect of Qlik's dual data type. The value is displayed in that format in which it was loaded for the first time. So if you are just "reformating" the same field, this might not work as expected.

Community Browser