Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Problems with number format in Chart

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.

Tags (3)
1 Solution

Accepted Solutions

Re: Problems with number format in Chart

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

5 Replies

Re: Problems with number format in Chart

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

Re: Problems with number format in Chart

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

- Marcus

Not applicable

Re: Problems with number format in Chart

Perfect. Thanks alot.

Not applicable

Re: Problems with number format in Chart

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

Re: Problems with number format in Chart

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

Community Browser