Number formats - showing 100,000,000 as £100m

[Anonymous]

What would I put in the 'format pattern' box to get 100,000,000 to be displayed as £100m?

Thanks

Greg

[alexandros17]

Try with

Num(100000000,'£ #.##0,00')/1000000

[Anonymous]

Is it possible to change the 'Format Pattern' setting in the following dialogue box to achieve the same result?

[#4]

Yes, in the script you can modify the SET MoneyFormat variable.

Edit: You can set the format, but not an expression to do calculations.

[Anonymous]

Not sure I understand.

[jonathandienst]

Hi

Just enter 'm' in the Million symbol box and 'b' in the Billion symbol box. You could also enter k or (000s) in the Thousand box. Qlikview will pick the appropriate symbol and scale the number accordingly in the chart

Oh, and format the number with as £#0

HTH

Jonathan

[Anonymous]

Hi Jonathan

Thanks for the response.  That's what I thought but nothing happened when I tried that. 

[ablaas · Accepted solution]

Those settings only work in charts (not pivot or single tables).

You must calculate the amount in the expression, and use the appropiate number format:

Eg:

Expression:

    sum(Amount)/1000000

Format (in the Format Pattern under the Number tab)

     £ #.##0m; -£ #.##0m

This way, the format will show correctly in the cell

[Anonymous]

Thanks Alejandro.