# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results 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.

1 Solution

Accepted Solutions
MVP

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

Hope it helps

10 Replies
MVP

=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!

MVP

might be a bit late

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

\$(vFormatNumber(parameter)) as paramFormat;
AutoGenerate 30;

```

regards

Marco

Not applicable
Author

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

Specialist II

Hi,

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

Thanks,

Lakshmikandh

Contributor

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

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

are you looking for similar out put

attached is the app