topic Re: number multiply by null value return 0? in QlikView

number multiply by null value return 0?

Tue, 21 Oct 2014 10:09:28 GMT

Hi all,

I have done a test. I have data like below :

load * inline

[pack,Inv,price

1,10,12

2,20,10

3,30,

];

Notice that there is no correspond price for pack '3'.

I have 2 straight tables and the dimensions are both 'pack'.

Chart A has only 1 expression: Amount: sum(Inv*price)

Chart B has 2 expression: Amount:sum(Inv*price) and Target: 30.

This is the outcome:

I am curious because in table B, Amount for pack '3' is 0 but not '-' (for null).

This will mislead users that the inventory of pack 3 is 0.

I want to show '-' in this case.How to fix it？

Many thanks!

Re: number multiply by null value return 0?

ali_hijazi — Tue, 21 Oct 2014 10:12:25 GMT

in Chart A go to presentation tab and uncheck suppress zero values

or update your expression to sum(aggr(sum(Inv*Price),Pack))

Re: number multiply by null value return 0?

PrashantSangle — Tue, 21 Oct 2014 10:19:36 GMT

Hi,

Write change your expression for Calculating Amount to

AGGR(SUM(Inv*Price))

Regards

Re: number multiply by null value return 0?

tresB — Tue, 21 Oct 2014 10:22:31 GMT

That is because of sum(), If there is a one-to-one relationship(like in your example) you could use use instead:

=Inv*price // without sum

Re: number multiply by null value return 0?

PrashantSangle — Tue, 21 Oct 2014 10:24:32 GMT

Hi,

You can also write like

if(isnull(Inv) or isnull(Price),'-',Sum(Inv*Price))

if(isnull(Inv) or isnull(Price) or len(trim(Inv))=0 or len(trim(Price))=0,'-',Sum(Inv*Price))

if(len(trim(Inv))>0 and len(trim(Price))>0,Sum(Inv*Price),'-')

if(not isnull(Inv) and not isnull(Price),Sum(Inv*Price),'-')

Regards

Re: number multiply by null value return 0?

stigchel — Tue, 21 Oct 2014 10:29:11 GMT

Or use

sum({<price={'>0'}>}Inv*price)

Re: number multiply by null value return 0?

Tue, 21 Oct 2014 10:58:10 GMT

use this

If(isnull(Inv) or Isnull(Price) or len(trim(Inv))=0 or len(trim(Price))=0,'-',Sum(Inv*Price))

Re: number multiply by null value return 0?

Wed, 22 Oct 2014 01:44:14 GMT

Thank you all, there are many ways to solve this issue as you have proposed. I think tresesco told the truth.

It return 0 because i used 'sum'. Thank you very much.