Possible to autoformat numbers? (IE 1,700,000=1.7m and 700,000=700k)

[#1]

Hello,

I am developing a chart in which the accounts that will be viewed vary GREATLY by size and some will have sales in tens of millions while others in the low thousands; is there a way for number formats to automatically adjust to display a short num format?

I can do it manually by adding like "/1000" at the end of the expression and then in the number format adding a 'K' to the end so 10,000 would display as 10k but this will not be very dynamic when accounts go into the millions. For example a 10,000,000 account would show as 10,000k which is kind of confusing (ten thousand thousand).

Any help or tips are appreciated.

Thanks,

Stephan

[rbecher]

Hi Stephan,

you can use an expression like this:

=If(Value > 1E6, Round(Value/1E6, 1) & 'M',
   If(Value > 1E3, Round(Value/1E3, 1) & 'k',
     Value))

But a standard format code would be better obviously.. Also, the division could lead into rounding issues.

- Ralf

[#3]

Hey thanks for the quick reply!!

This may be a silly question but would your expression be used in my actual expressions section where I would replace 'Value' with my current calculation? Or would it go somewhere in the number formatting tab?

Thanks!

[rbecher]

Correctly, just replace Value with your expression.

[#5]

hmmm; I tried plugging it in and It's saying no data to display even though it says expression is OK.

My current expression is:

=If((sum($(vMetric))) > 1E6, Round((sum($(vMetric)))/1E6, 1) & 'M', 

   If((sum($(vMetric))) > 1E3, Round((sum($(vMetric)))/1E3, 1) & 'k', 

     (sum($(vMetric)))))

Where I used sum($(vMetric)) in place of Value because I am using buttons to change the metric (from dollars to quantity sold); could this be causing an issue?

[#6]

Hm I took out the symbols ( & 'M') and it works now; I am using this in a line chart and adding the M makes the resulting value not a regular number and thus cannot really be measured. Now I need to find out how to add the M and K using the number tab I guess?

[#7]

Try converting the result of the rounding to text using the text() formula

Sorry, just saw that you are using it in a line chart. Of course a text will not help anything there ...

But why don't you just use the default possibility of adding 'm' and 'k' from the numbers tab anyways?

[jerem1234]

Try using the dual function:

=If((sum($(vMetric))) > 1E6, dual(Round((sum($(vMetric)))/1E6, 1) & 'M', Round((sum($(vMetric)))/1E6, 1)),

   If((sum($(vMetric))) > 1E3, dual(Round((sum($(vMetric)))/1E3, 1) & 'k', Round((sum($(vMetric)))/1E3, 1)),

     (sum($(vMetric)))))

Hope this helps!

[#9]

Unless I am crazy; that functionality does not seem to work for me. How is it correctly implemented?

I put a K in thousands symbol, M in million, and B in billions but it does absolutely nothing.

[#10]

This has to do with the if-statement. Eliminate that and just put in sum($(vMetric)) and let the number tab take care of the rest. Than the 'k', 'm' and 'b' should appear. I have an example application for you, but still need to figure out how to upload it here ..

Here you go:

number_format.qvw

-- edit: This only shortens the axis though, not the numbers in the chart. --