Converting the format of numbers to k,mn,bn

[nikhilg19]

Hi guys,
I need a workflow to get the required output.

951,001 ---> 951 k
1,368,890 ---> 1.4 mn
1,920,848 ---> 1.9 mn
1,128,038,087 ---> 1.1 bn
2,280,467,892 ---> 2.3 bn

I have an idea of counting the number of digits (for k,mn or bn) and then taking the first two digits, also rounding off the third digit.
Please help me how to to do this in alteryx.

[Thableaus · Accepted solution]

Hi @nikhilg19 

See solution:

- Change field to Int Type (Number)

- Calculates log of the Number

- Creates a rule that says:

IF log < 3 no change

3 <= log < 6 means that the rounded Number/10^3 adds up a k at the end

6 <= log < 9 same thing, but divided by 10^6 and mn at the end

else (log >=9) rule applied to bn.

See if it fits you. WF appended.

Cheers, 

[nikhilg19]

Perfect!
Exactly what I needed.
Thank you very much @Thableaus

[SamDesk]

Hi @nikhilg19, 

I've attached a workflow that should produce the output you're looking for. It works by appending a list of possible multiples and their unit suffix (k, mn, etc) to every number record. It then attempts to divide every number by each possible multiple. Checking the number of numbers in results, it removes any that are less than 2 (i.e. divided it below 1) and then keeps the minimum length remainder that successfully divided. And finally, formatting the remainder to include the selected suffix.

Sam :)

[nikhilg19]

Thanks for the solution @SamDesk :D

[nikhilg19]

hi @Thableaus  do you know why is it showing 605174 as 605.2k insted of 605k ?

If [Log] < 3 then [Number]
ELSEIF [Log] >= 3 and log < 6 THEN
TOString(Round([Number]/ POW(10,3),.1))+
"k"
ELSEIF [log] >= 6 and log <9 THEN
TOString(Round([Number]/ POW(10,6),.1))+
"m"
ELSE ToString(Round([Number]/POW(10,9),.1)) + "bn"
ENDIF

[SamDesk]

Hi @nikhilg19,

It is because of the rounding multiple chosen in the k part of your formula:

ToString(Round([Number]/ POW(10,3),.1))

This ".1" forces the round to round numbers to include a decimal place.

In your previous example of:

951,001 ---> 951 k

This happened because 951001 rounds to 951.0 using a round multiple of .1 and Alteryx will display this as simply 951 for simplicity.

Sam :)

[SamDesk]

Hi @nikhilg19, @Thableaus 

Also in your formula, I notice that some of your [Log] fields have been supplanted with just the 'log' function. I think all occurrences of 'log' are supposed to be [Log].

Sam :)

[nikhilg19]

So @SamDesk  should I keep 1 instead of .1?

Its actually working but not sure if that the way to get 971k when we have 970,899 (for ex)

[SamDesk]

Hi @nikhilg19,

If you always want your k values to be formatted like 653k, 244k, etc. then you should change the .1 to 1.

970,899 will round to 971k when using a multiple of 1.

Sam :)