Format numbers to contain 3 characters at max

[#1]

Hi Folks,

is there a way to use the Numbers tab within the chart properties or the num() function to format a number to contain at max 3 characters in combination with the Thousand / Million / Billion Symbol options?

Scenario:

1 --> 1

12 --> 12

123 --> 123

1234 --> 1,2 (k)

12345 --> 12 (k)

123456 --> 123 (k)

1234567 --> 1,2 (M)

12345678 --> 12 (M)

123456790 --> 123 (M)

Any suggestions?

[MarcoWedel]

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;

after creation of the variables in the script, you can simply use them in chart expressions like:

The solutions using the Dual function preserve the original numerical value i.e. sorting and calculations with the formatted value are still possible like with the original value.

regards

Marco