Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Number Formatting


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

1 Solution

Accepted Solutions
morganaaron
Specialist
Specialist

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.

View solution in original post

5 Replies
alex_millan
Creator III
Creator III

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

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.

View solution in original post

Not applicable
Author

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

Not applicable
Author

Absolutely perfect solution Aaron.

Thank you so much for your help!

Kindest Regards

Emma

puttemans
Specialist
Specialist

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