Custom format charts with numerical abbreviation (K, M, B, T, etc.) for exporting

[MichelLalancette]

Is there a way to format my numbers using abbreviations in tables (and sometimes charts) so that when the number is greater than 1000, it becomes 1k, then > 1 000 000 it becomes 1 M, etc.

I'm aware of the load script "NumericalAbbreviation" feature but this does not help at all in tables. It seems there is no way to auto format them the same as say, a bar graph (that does this automatically).

I'm aware I can force a format using logic such as 

If (fabs(sum(<myNumber>)) > 1000, num(sum(<muNumber>), '#,##0k')

While this works, it's very inconvenient (lots of code repetition even though there are some smaller examples out there) and does not export well to Excel. The numbers do not go back to their original values once you apply this formatting, they become simple strings, and rather than having the full number when right-clicking -> view data or exporting to excel, you get "1 k".

I would like to go from this:

To this:

And still retain the ability to export as the full number in Excel or viewing the raw data behind the chart.

Thanks,

Michel

[stevedark]

Hi @MichelLalancette 

I have done similar for file sizes like this:

Create a variable called vFormatSize with the following code:

dual(if( $1 > 1073741824, num( $1 / 1073741824, '#,##0.0 GB'), 
if( $1 > 1048576, num( $1 / 1048576, '#,##0.0 MB'),
if( $1 > 1024, num( $1 / 1024, '#,##0.0 KB'), 
num($1, '#,##0 bytes')))), $1)

And then wrap the expression in a call to the variable:

$(vFormatSize(sum(FileSize)))

This then formats each file size to the right unit, but because it's a dual it still sorts and totals correctly.

You could do the same with K,M,B,T and the code would be tidier as you have multiples of 1000 rather than 1024.

This is all great as long as you are keeping the data in Sense. Sadly as soon as you export the text representation is sent in preference to the numeric and you can't do anything with it in Excel.

I'm not aware of a route around this.

Hope that is of some interest / help.

Steve

[MichelLalancette]

I did do that and in my original solution as it's much shorter than most other ways (no code repeating). Too bad it's then forced to export as text 😞

Thanks,

Michel