topic Re: Possible to autoformat numbers? (IE 1,700,000=1.7m and 700,000=700k) in QlikView

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

Tue, 03 Dec 2013 19:13:09 GMT

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

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

rbecher — Tue, 03 Dec 2013 19:17:40 GMT

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

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

Tue, 03 Dec 2013 19:29:21 GMT

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!

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

rbecher — Tue, 03 Dec 2013 19:39:53 GMT

Correctly, just replace Value with your expression.

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

Tue, 03 Dec 2013 19:46:45 GMT

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?

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

Tue, 03 Dec 2013 19:54:02 GMT

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?

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

Tue, 03 Dec 2013 20:08:53 GMT

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?

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

jerem1234 — Tue, 03 Dec 2013 20:19:45 GMT

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!

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

Tue, 03 Dec 2013 20:19:52 GMT

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.

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

Tue, 03 Dec 2013 20:26:25 GMT

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

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

Tue, 03 Dec 2013 20:27:23 GMT

This does not seem to display the M/K symbols. (I am using a line chart; I don't know if anything other than actual numbers can really work here)

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

jerem1234 — Tue, 03 Dec 2013 20:41:38 GMT

It seems to be on an example I made:

Think you will have to put expression default for that expression on the numbers tab. If you have a number format, it will take the number value instead of the text of the dual function. Also, be careful in the sorting, in the example I gave 177M will be placed at the same level as 177k. So you will probably have to use just:

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

and

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

Hope this helps!

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

Tue, 03 Dec 2013 21:46:28 GMT

Hi, this seems to be working best so far once I changed the number setting to expression default, however it is rounding to whole numbers. I tried tweaking the value after the condition which I believe is what sets how many decimals it rounds to but that does not seem to be working. I would like one decimal point to display as a lot of the values are in single digit millions where it is not appropriate to round something like 4.6M and 5.4M boht to 5M.

Where would I adjust this?

Thanks!

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

Tue, 03 Dec 2013 23:21:26 GMT

Yeah the issue is moreso the numbers in the chart rather than the Axis... any ideas?

Thanks,

Stephan

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

Wed, 04 Dec 2013 07:53:37 GMT

Yeah, that got me confused as well. The second parameter of the round is not the number of decimal places. The correct syntax is: round(xyz,0.1) (or 1, 0.01, 0.001, 0.0001, ...)

Best regards!