Expression to display non null and zero values

[ts3st1000]

Hi, I am trying to display a table and i would like not to rows with null or zero values.

Can you please suggest a measure expression for that please ?

Thanks very much.

[devarasu07]

Hi,

yes it can be done in many ways.

method 1:

suppress null value in your chart dimension

method 2:

cal.dimension

if( len(trim(Account))>0, Account) // make sure suppress null.

Method 3:

using set analysis

sum( {<Account={"=len(trim(Account))>0"}>} Sales)

method 4:

using back end script method, try to make non distinct value (master dimension should contain all the possible value and not null) and exclude 0 u can use where clause

Fact:

load * from table.qvd (qvd) where  Sales <>0;

refer to the sample,

Thanks,

Deva

[ts3st1000]

Thank you Devarasu. I am actually looking for an expression that ignores both null and Zero values. any suggestion how to do that please ?

[devarasu07]

Hi,

have your tried above suggestion it should work. would be able to share the mock data and expected output in excel format that helpful for us to check quickly & give u suggestion. thanks

Sum( {$<Account={"=len(Account)>0"},Sales={"<>0"}>}Sales)

let's say dimension : Account used in the chart object

Account={"=len(Account)>0  // this condition will exclude null/blank account from the chart

Sales={"<>0"} // this will exclude 0 and it will only allow >0 and negative values.

Thanks

Deva

[ts3st1000]

Thanks Deva, i have just tired that and it did not work. Here is an example to illustrate what i am trying to do.

account	country	sales1	sales2
A	Japan	100	10
B	China	0	20
C	India	0	0
D	Malaysia	200	30
E	Indonesia		40

i would like to remove all rows that have 0 or null sales and be left with this:

account	country	sales1	sales2
A	Japan	100	10
D	Malaysia	200	30

Can you please suggest an expression that would work for this case. thanks very much.

[devarasu07]

Hi,

try to create like below cal. dimension and suppress null value.

=aggr(if ( alt(sum(sales1),0) <>0 and alt(sum(sales2),0)<>0,account ),account,country)