Number Formatting

[#1]

Please could someone assist with the number formatting in my table.

Eg.  Jan = £511,767.08

        Feb = £591,711.44

        March  = £144,465.71

TOTAL: £1,247,944.23

==================================

Expression is

=SUM (lineVa*np)

Format = Money

£#,##0.00;-£#,##0.00

=====================================

What I would like to see is:

Jan = £512K

Feb = £592K

March = £144K

with a TOTAL of £1.2 M

Any help would be gratefully received from this newbie!

Kind Regards

[alex_millan]

Hi Emma,

perhaps you can write your expression dividing by 1000:

=SUM (lineVa*np)

Then in the tab number, set the format pattern for this expression:

     € #.##0 K

Hope it helps,

Regards

[morganaaron · Accepted solution]

Hi Emma,

You could change your expression to read:

if(Sum(lineVA*np) > 1000000,
Money((Sum(lineVA*np)/1000000), '£#.0m'),
Money((Sum(lineVA*np)/1000), '£##0K'))

Then in the number tab, leave it as default expression. That will give anything greater than a million a format of XXX.X million, and anything smaller than a million a format of XXX thousand - just be aware that if it's smaller than a thousand this will cause issues, if it is just alter your thousand format to ###K or add in another if statement to differ for any number smaller than a thousand.

[#4]

Hi Alex

Thank you for your reply - I'm very grateful as really new to this and had

no training!

Your assistance has got me closer to what I require but not quite:

It now looks like this:

I would like

1.2 M (this is the total qlikview automatically puts about the column in

bold) (i think!)

511K

592K

144K

Could you help me take your solution to this ?

Thank you

Emma

[#5]

Absolutely perfect solution Aaron.

Thank you so much for your help!

Kindest Regards

Emma

[puttemans]

Wouldn't you need a rounding somewhere neither?

if(Sum(lineVA*np)/1000000 > 1, Money((Round(Sum(lineVA*np)/1000000)), '£#.0m'),
     if(Sum(lineVA*np)/100000 > 1,  Money((Round(Sum(lineVA*np)/1000)), '£##0K'),

                Sum(lineVA*np)/1000))