topic Re: Number Formatting in QlikView

Number Formatting

Thu, 12 Mar 2015 10:12:24 GMT

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

Re: Number Formatting

alex_millan — Thu, 12 Mar 2015 10:46:18 GMT

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

Re: Number Formatting

morganaaron — Thu, 12 Mar 2015 10:47:54 GMT

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.

Re: Number Formatting

Thu, 12 Mar 2015 10:54:40 GMT

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

Re: Number Formatting

Thu, 12 Mar 2015 10:58:16 GMT

Absolutely perfect solution Aaron.

Thank you so much for your help!

Kindest Regards

Emma

Re: Number Formatting

puttemans — Thu, 12 Mar 2015 10:58:42 GMT

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