topic Re: Format numbers to contain 3 characters at max in QlikView

Format numbers to contain 3 characters at max

Mon, 25 Aug 2014 08:08:49 GMT

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?

Re: Format numbers to contain 3 characters at max

jagan — Mon, 25 Aug 2014 08:32:21 GMT

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

123

1234

12345

123456

1234567

12345678

123456790];

Regards,

Jagan.

Re: Format numbers to contain 3 characters at max

tresB — Mon, 25 Aug 2014 08:36:48 GMT

Try like:

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

Re: Format numbers to contain 3 characters at max

jonathandienst — Mon, 25 Aug 2014 08:48:55 GMT

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

123

1234

12345

123456

1234567

12345678

123456789

1234567890

];

Output:

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

Re: Format numbers to contain 3 characters at max

MarcoWedel — Mon, 25 Aug 2014 09:03:15 GMT

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

Re: Format numbers to contain 3 characters at max

Mon, 25 Aug 2014 09:05:44 GMT

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

Re: Format numbers to contain 3 characters at max

jagan — Mon, 25 Aug 2014 09:16:49 GMT

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.

Re: Format numbers to contain 3 characters at max

Mon, 25 Aug 2014 09:23:33 GMT

Hi Jagan,

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

Regards

Ankit

Re: Format numbers to contain 3 characters at max

jonathandienst — Mon, 25 Aug 2014 09:23:47 GMT

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

Re: Format numbers to contain 3 characters at max

Mon, 25 Aug 2014 11:42:23 GMT

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.

Re: Format numbers to contain 3 characters at max

MarcoWedel — Mon, 25 Aug 2014 12:27:43 GMT

maybe also helpful:

SET vSize = Div(Log10($1),3);
SET vFormatNumber = Dual(Num($1/Pow(10,3*$(vSize($1))),'#,##0')&' '&Pick($(vSize($1)),'K','Mil','Bil','Tril'),$1);

LOAD *,
     $(vFormatNumber(parameter)) as paramFormat;
LOAD pow(10,Rand()*14) as parameter
AutoGenerate 30;

after creation of the variables in the script, you can simply use them in chart expressions like:

The solutions using the Dual function preserve the original numerical value i.e. sorting and calculations with the formatted value are still possible like with the original value.

regards

Marco