Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.
alteryx Community

# Alteryx Designer Discussions

SOLVED

## Converting the format of numbers to k,mn,bn

7 - Meteor
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.
9 REPLIES 9
17 - Castor

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,

7 - Meteor
Perfect!
Exactly what I needed.
Thank you very much @Thableaus
11 - Bolide

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 :)

7 - Meteor
Thanks for the solution @SamDesk :D
7 - Meteor

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

11 - Bolide

Hi @nikhilg19,

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

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

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

`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 :)

11 - Bolide

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 :)

7 - Meteor

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)

11 - Bolide

Hi @nikhilg19,

If you always want your 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 :)

Labels