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: 
OmarBenSalem

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
juraj_misina
Luminary Alumni
Luminary Alumni

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.

View solution in original post

12 Replies
brunobertels
Master
Master

Hi

In your straight table did you chek the output format ?

Capture.PNG

sunny_talwar

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

OmarBenSalem
Author

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
Author

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';

sunny_talwar

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
Author

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

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Omar,

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

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

OmarBenSalem
Author

Yes Juraj,

That was one of my first tries:

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

Capture.PNG

juraj_misina
Luminary Alumni
Luminary Alumni

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.