Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ts3st1000
New Contributor

Expression to display non null and zero values

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.

5 Replies
devarasu07
Honored Contributor II

Re: Expression to display non null and zero values

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,

Capture.JPG

Thanks,

Deva

ts3st1000
New Contributor

Re: Expression to display non null and zero values

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
Honored Contributor II

Re: Expression to display non null and zero values

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
New Contributor

Re: Expression to display non null and zero values

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

 

accountcountrysales1sales2
AJapan10010
BChina020
CIndia00
DMalaysia20030
EIndonesia40

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

 

accountcountrysales1sales2
AJapan10010
DMalaysia20030

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

devarasu07
Honored Contributor II

Re: Expression to display non null and zero values

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)


Capture.JPG

Capture.JPG