Problems with number format in Chart

[#1]

Using a Chart, I want to display a range of numbers. For formatting purposes i am using the following code:

if((sum([InvoiceNetValue]) > 1000000),
       num(sum([InvoiceNetValue]/1000),'#.##0M;-#.##0M'),
            if((sum([InvoiceNetValue]) > 10000),
                 num(sum([InvoiceNetValue]/1000),'#.##0K;-#.##0K'),         
num(sum([InvoiceNetValue]/1000),'#.##,##K;-#.##,##K')))

This works great. Bigger numbers are expressed in Millions while smaller numbers are expressed as #.##0K.

However, I want to exclude some values from this chart. I created the following code.

Sum(if([CustomerKey] <> '106180'
     AND [CustomerKey] <> '104300'
     AND [CustomerKey] <> '105680'
     AND [CustomerKey] <> '305000'
     AND [CustomerKey] <> '305500'
     AND [CustomerKey] <> '51062'
     AND [CustomerKey] <> '50339',
if(([InvoiceNetValue] > 1000000),
  num([InvoiceNetValue]/1000,'#.##0M;-#.##0M'),
       if(([InvoiceNetValue] > 10000),
            num([InvoiceNetValue]/1000,'#.##0K;-#.##0K'),    
               num([InvoiceNetValue]/1000,'#.##,##K;-#.##,##K'))),    
num(0,'#.##,##K;-#.##,##K')))

The values are correct, however the format doesnt work. It just displays the plain number without the M and K's. How can I fix this?

It seems that Qlikview completely ignores the num() format.

[CELAMBARASAN · Accepted solution]

Try with this

if(Sum({<[CustomerKey]-={'106180' , '104300','105680' , '305000'  , '305500'  ,'51062'  , '50339'}>} [InvoiceNetValue]) > 1000000,

Num(Sum({<[CustomerKey]-={'106180' , '104300','105680' , '305000'  , '305500'  ,'51062'  , '50339'}>} [InvoiceNetValue])/1000,'#.##0M;-#.##0M'),

Num(Sum({<[CustomerKey]-={'106180' , '104300','105680' , '305000'  , '305500'  ,'51062'  , '50339'}>} [InvoiceNetValue])/1000,'#.##0K;-#.##0K'))

Hope it helps

Celambarasan

[marcus_sommer]

The logic within the second example isn't like the first. You formatted inside the expression and not the expression it self.

- Marcus

[#4]

Perfect. Thanks alot.

[#5]

Yes but I have no idea how else it could be formatted.

[marcus_sommer]

If you used the logic from first example it will work, I would to drop the num() parameter for negative values.

Perhaps an alternatively could be to create an extra flag-field for these customer and use this in a listbox - it avoids complexe set analysis statements in the expression.

- Marcus