topic Number formatting - millions to billions in QlikView

Number formatting - millions to billions

Tue, 06 May 2014 14:34:07 GMT

Hi everyone,

I'm a beginner Qlikview user and am still struggling with anything past making simple charts and list boxes.

In this case, I have a 'Market Size' KPI text box, with a formula of =sum(Amount)/1000000,'$#,###B'). In other words, I have a number that comes out in billions, with a nice B at the end of it. However, when I drill down on my chart to focus on individual businesses within that market, I'm really dealing with market sizing of millions, rather than billions. The result is that my KPI text box becomes an ugly $0.0000343B.

Does anyone know how I can instruct the KPI text box to switch to millions, and switch the B with an M at the end, if the numbers become small enough? I have a hunch that I need to rewrite the expression formula as an if statement, but I'm not sure how.

Thanks in advance!

Re: Number formatting - millions to billions

alexandros17 — Tue, 06 May 2014 14:59:05 GMT

=If(sum(Amount)/1000000000 > 1,Num(sum(Amount)/1000000000,'$#,###B'), Num(sum(Amount)/1000000,'$#,###M'))

Hope it helps

Re: Number formatting - millions to billions

Tue, 06 May 2014 15:37:21 GMT

Thank you, Alessandro! This worked perfectly.

As a follow-up question, if I encountered a number that was less than $1 million, how would I modify that expression to add a second condition to express that number in thousands?

Thank you!

Re: Number formatting - millions to billions

MarcoWedel — Mon, 30 Jun 2014 20:05:58 GMT

might be a bit late

but maybe also helpful:

SET vSize = Div(Log10($1),3);
SET vFormatNumber = Dual(Num($1/Pow(10,3*$(vSize($1))),'#,##0')&' '&Pick($(vSize($1)),'K','Mil','Bil','Tril'),$1);

LOAD *,
    $(vFormatNumber(parameter)) as paramFormat;
LOAD pow(10,Rand()*14) as parameter
AutoGenerate 30;

regards

Marco

Re: Number formatting - millions to billions

Wed, 20 Aug 2014 00:54:11 GMT

Thank you, Marco - that is awesome! Now QlikView needs to add that so we can export w/out formatting.

Re: Number formatting - millions to billions

lakshmikandh — Tue, 27 Sep 2016 19:28:06 GMT

Hi,

You can change the auto number formatting symbol from "G" to "B" in javascript

Please check this https://community.qlik.com/docs/DOC-17461

Thanks,

Lakshmikandh

Re: Number formatting - millions to billions

Anonymous — Sat, 05 Nov 2016 11:10:17 GMT

Marco love this solution! Just what I was looking for. Thanks!

Re: Number formatting - millions to billions

max_potass — Tue, 25 Jul 2017 12:18:20 GMT

Thank you so much. I almost lost it over this stupid problem.

Re: Number formatting - millions to billions

Wed, 23 Aug 2017 07:11:31 GMT

Hi Marco,

Thanks for the solution! But it seems that it won't work for negative value. Anything that I need to change so that it can format both positive and negative value? Thanks again!

*Edit*

Manage to get it to work after change the following by include the fabs function:

SET vSize = Div(Log10(fabs($1)),3);

Re: Number formatting - millions to billions

ziadm — Fri, 20 Oct 2017 20:05:35 GMT

are you looking for similar out put

attached is the app

Re: Number formatting - millions to billions

writetoprithvi — Mon, 28 Sep 2020 17:26:27 GMT

Great. Thanks.