Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Format Number (in Thousand)

Hi guys,

just a quick question,

How to display number with thousand format in qlik sense ?

Usually in Excel, I just add dot or comma in custom format to display my number in thousand.

Can Qlik sense do that ?

Please advice

Thanks

Henry

1 Solution

Accepted Solutions
Employee
Employee

Re: Format Number (in Thousand)

Hi again,

There is currently no support for automated suffixes when formatting (we are looking into this). However it is still possible to achieve what you want by formatting directly in the measure expression.

Assuming you have a measure Sum(Sales) then the expression would look like this:

if(Sum(Sales) > 1000000,  num(Sum(Sales)/1000000, '#.##0,##M', ',', '.'),

    if(Sum(Sales) > 1000, num(Sum(Sales)/1000,  '#.##0,##K', ',', '.'), num(Sum(Sales), '#.##0,##', ',', '.')))

Which would result in something like the following:

Formatting.PNG

28 Replies

Re: Format Number (in Thousand)

I don't understand your question. The screenshot you posted already shows the thousand separator in action.


talk is cheap, supply exceeds demand
saurabh5
Contributor II

Re: Format Number (in Thousand)

In the screen shot it shows that it is seperating just make a change in your format pattern to ###,##0

Not applicable

Re: Format Number (in Thousand)

Let say my number is 10.000.000 (ten million), I want my number show 10 (which is in million).

in Excel, I usually just add (dot) to cover thousand.

vigneshvs
Contributor II

Re: Format Number (in Thousand)

Hi Henry,

By default the values gets formatted in qlik sense...

Like if it is 100,000 then the result is "100k"

So make the number formatting to auto.

Thanks,

Vignesh

Not applicable

Re: Format Number (in Thousand)

But when I am using table, the result is not 100K but 100.000

vigneshvs
Contributor II

Re: Format Number (in Thousand)

Yes Your right in table it will not get formatted...

Suggestions:

1.May be the Qlik tech team can fix this in the next release.

2.Use of third party extensions may resolve your needs.

Not applicable

Re: Format Number (in Thousand)

Hi  Henry

to format numbers , you can either do it via some script using num#(field_name,'###,##0') function, or else you can just simply go to the Number tab , select Number, then set how you would like your numbers to b displayed in that particular object.

Hope this helps

Best Regards

Dlamini

Employee
Employee

Re: Format Number (in Thousand)

Hi Henry,

What you want is typically achieved by having a measure with the same value that you would like to display. Since you want the numbers to be in thousands you simply divide the measure by 1000 (e.g. sum(Sales)/1000) and then adjust the format settings to use the desired separators;

Number formatting: Custom

Decimal sep: ,

Thousands sep: .

Pattern: #.##0

Regards,

Miralem

plimdelondres
New Contributor III

Re: Format Number (in Thousand)

Try changing the format pattern to:

###,##0

Community Browser