topic Re: Ignore Null values from Total function in QlikView

Ignore Null values from Total function

pradnyat — Thu, 09 Oct 2014 14:37:23 GMT

Hi All,

I have 1 straight table with below dimensions and Expressions:

Plan ,Sales and Total Sales.

The issue is there is Sales for some Null(-) Plans. So when I am using total function in Total Sales, then its including the sales for null plans also,even after selecting Suppress Null Values for Plan dimension.

Is there any way to ignore the sales for null plans from Total function.

Thanks in Advance.... !

Re: Ignore Null values from Total function

JonnyPoole — Thu, 09 Oct 2014 16:51:04 GMT

The suppress nulls works on the aggregated values , not the detail records.

A common technique is to use and IF statement on all expressions:

sum ( If ( not isnull(Plan) , Sales) )

sum ( If ( not isnull(Plan) , [Total Sales]) )

Then the Sales and Total Sales will be null when the Plan is Null and the row will be suppressed from the chart.

Re: Ignore Null values from Total function

Vegar — Thu, 09 Oct 2014 20:20:29 GMT

A set expression should also work.

sum (  {<Plain ={"*"}>}Sales)

Re: Ignore Null values from Total function

sunny_talwar — Thu, 09 Oct 2014 20:28:28 GMT

Please see the attached qvw file. Is this what you were trying to do?

Best,