number multiply by null value return 0?

[#1]

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!

[ali_hijazi]

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

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

[PrashantSangle]

Hi,

Write change your expression for Calculating Amount to

AGGR(SUM(Inv*Price))

Regards

[tresesco · Accepted solution]

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

[PrashantSangle]

Hi,

You can also write like

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

or

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

Or

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

or

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

Regards

[stigchel]

Or use

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

[#7]

use this

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

[#8]

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.