Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting the format of numbers to k,mn,bn

nikhilg19
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.
Please help me how to to do this in alteryx.
9 REPLIES 9
Thableaus
17 - Castor
17 - Castor

Hi @nikhilg19 

 

See solution:

 

example1.PNG

 

- 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
7 - Meteor
Perfect!
Exactly what I needed.
Thank you very much @Thableaus
SamDesk
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.

 

Capture.PNG

 

Sam :)

nikhilg19
7 - Meteor
Thanks for the solution @SamDesk :D
nikhilg19
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

 

Capture.png

SamDesk
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.

 

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
11 - Bolide

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

SamDesk
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