Number formatting - millions to billions

[#1]

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!

[alexandros17 · Accepted solution]

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

Hope it helps

[#3]

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!

[MarcoWedel]

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

[#5]

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

[lakshmikandh]

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

[Anonymous]

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

[max_potass]

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

[#9]

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

[ziadm]

are you looking for similar out put

attached is the app