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: 
Not applicable

Number formatting - millions to billions

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!

1 Solution

Accepted Solutions
alexandros17
Partner - Champion III
Partner - Champion III

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


Hope it helps

View solution in original post

10 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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


Hope it helps

Not applicable
Author

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;

QlikCommunity_Thread_38556_Pic1.JPG.jpg

QlikCommunity_Thread_38556_Pic3.JPG.jpg

regards

Marco

Not applicable
Author

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

lakshmikandh
Specialist II
Specialist II

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
Not applicable
Author

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

max_potass
Creator
Creator

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

Not applicable
Author

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
Specialist
Specialist

are you looking for similar out put

attached is the app

Capture.PNG