Converting the format of numbers to k,mn,bn

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.

Input

Data Investigation

Documentation

Accepted answers

Thableaus

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,

WFsol1.yxmd

All comments

Thableaus

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,

WFsol1.yxmd

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

Format Numbers to Units.yxmd

nikhilg19

Thanks for the solution @SamDesk

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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335