topic Re: Short number in QlikView

Short number

Mon, 23 Nov 2015 13:18:33 GMT

All,

Setting thousand/million/billion symbol does not work with straight table/pivot table charts. I am using a pivot chart to display numbers and would like to achieve the same short number format. E.g:

123 -> 0.1K

12345 -> 1.2K

1234567 -> 1.2M

12345678910 -> 1.2B

Any smart way to achieve this?

Thank you!

Re: Short number

t_chetirbok — Mon, 23 Nov 2015 19:33:06 GMT

Hello!

I'm not sure that it's smart, but it works:

round(if(VALUE/1000000000>=1, VALUE/1000000000,

if(VALUE/1000000>=1, VALUE/1000000,

if(VALUE/1000>=1, VALUE/10000, VALUE/1000))), 0.01) &

if(VALUE/1000000000>=1, ' B',

if(VALUE/1000000>=1, 'M', ' K'))

Re: Short number

Mon, 23 Nov 2015 20:06:02 GMT

Tatsiana, thanks for the attempt. This problem has to be solved in the "custom formatting" definition. The developers need to address this asap using the Excel custom number formatting pattern, or Qlik will not be getting much business from Excel users.

Re: Short number

Mon, 23 Nov 2015 20:14:55 GMT

Hi,

Thanks for the reply. It does work but i already have a massive nested IF statement. Adding another set of nested IFs would not be the best practice. Any other idea?

Thanks.

Re: Short number

oknotsen — Mon, 23 Nov 2015 20:19:45 GMT

You get my vote for joke of the day.

No offence, but this tiny thing is not the reason that will keep people using XLS for the wrong reason. If it would, those people clearly have no clue what Qlik has to offer them.

Re: Short number

MarcoWedel — Tue, 24 Nov 2015 00:14:25 GMT

Hi,

maybe helpful:

Re: Format numbers to contain 3 characters at max

Re: dynamically changing scale in text box

regards

Marco

Re: Short number

Tue, 24 Nov 2015 02:53:35 GMT

Thanks, this is very helpful. Has anyone ever tried to code an Excel

custom format such as "#,##0.0,,;[red](#,##0.0,,);-" ? It takes about 10

seconds to write in Excel - it means: 0s displayed as "-", negative

numbers displayed in red font in parentheses, numbers all divided by a

million, one decimal of precision.