Format numbers to contain 3 characters at max

[#1]

Hi Folks,

is there a way to use the Numbers tab within the chart properties or the num() function to format a number to contain at max 3 characters in combination with the Thousand / Million / Billion Symbol options?

Scenario:

1 --> 1

12 --> 12

123 --> 123

1234 --> 1,2 (k)

12345 --> 12 (k)

123456 --> 123 (k)

1234567 --> 1,2 (M)

12345678 --> 12 (M)

123456790 --> 123 (M)

Any suggestions?

[jagan · Accepted solution]

Hi,

Try like this

LOAD

Value,

If(Len(Value) > 7, Floor(Value/1000000) & ' (M)',

  If(Len(Value) > 6, Num(Value/1000000, '###.0') & ' (M)',

  If(Len(Value) > 4, Floor(Value/1000) & ' (K)',

  If(Len(Value) > 3, Num(Value/1000, '###.0') & ' (K)', Value)))) AS Num

INLINE [

Value

1

12

123

1234

12345

123456

1234567

12345678

123456790];

Regards,

Jagan.

[tresesco]

Try like:

=If((YourNum/1000000)>1, Round(YourNum/1000000)&'M', If((YourNum/1000)>1, Round(YourNum/1000)&'K', YourNum))

[jonathandienst]

Hi

How about this:

LOAD Dual(tValue2 & Symbol, Orig) As Value;

LOAD *, Round(tValue, tRound) As tValue2;

LOAD *,

  Orig / Pow(10, tLog) As tValue,

  Pow(10, tLog) As Pwr,

  If(tLog = tLog2, 0.1, 1) As tRound,

  Pick(Match(tLog, 0, 3, 6, 9, 12), '', 'k', 'M', 'B', 'T') As Symbol;

LOAD Value As Orig,

  Round(Floor(log10(Value)) - 1,3) As tLog,

  Floor(log10(Value)) As tLog2

Inline

[

  Value

  1

  12

  123

  1234

  12345

  123456

  1234567

  12345678

  123456789

  1234567890

];

Output:

1

12

123

1.2k

12k

123k

1.2M

12M

123M

1.2B

Created as dual values with the underlying value the original amount

See attachment.

HTH

Jonathan

[MarcoWedel]

http://community.qlik.com/message/558928#558928

[#6]

Hi,

I think all of the above suggestions are perfect.However if you want to do it through Number Tab then

In the Properties<-Number<-Select the Expression<-Number Format Settings<-Fixed to ...or <your requirement>.

and also define symbols.

Thanks

[jagan]

Hi Ankit,

It is not possible using Number tab, instead you can use it in expression and select Expression default in Number tab.

Regards,

Jagan.

[#8]

Hi Jagan,

Thanks for updating.I will keep that for my future references.

Regards

Ankit

[jonathandienst]

The tricky part is the rounding (1,12,123,1.2k,12k, 123k, 1.2M,...). The standard handling from the number tab can't handle that, my previous post does. You can do the same with this expression:

Dual(Round(Orig / Pow(10, Round(Floor(log10(Orig)) - 1, 3)),

     If(Round(Floor(log10(Orig)) - 1, 3) = Floor(log10(Orig)), 0.1, 1))

     & Pick(Match(Round(Floor(log10(Orig)) - 1, 3), 0, 3, 6, 9, 12), '', 'k', 'M', 'B', 'T'), Orig

)

HTH

Jonathan

[#10]

Thanks a lot! This seems to be the most robust solution. However, the script level was not feasible for me since this needs to be done with a variety of measures. I ended up using the solution suggested by Jagan (the line of if statements)) in combination with a parameterized variable.