Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
The logic within the second example isn't like the first. You formatted inside the expression and not the expression it self.
- Marcus
Perfect. Thanks alot.
Yes but I have no idea how else it could be formatted.
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