9 Replies Latest reply: Apr 10, 2018 6:23 AM by Sunny Talwar

# Excluding Null Values in KPI

Hello All,

I wanted to exclude the Null values from the KPI Chart for sales

I have tried the following equation,

If(Channel= Null(),0

if(Channel='xyz',

Sum({<condition={'bat'}>}sales),

if(Channel<>'xyz',

Sum({<condition={'ball'}>}sales))))

However I am not getting the correct result

Total sales = 185847.

So wanted to know if there are any changes to be made to the equation.

• ###### Re: Excluding Null Values in KPI

maybe this:

Sum( {<condition={'ball'}>} if( not isnull(channel) and len(trim(channel))>0, sales ) )

and

Sum( {<condition={'bat'}>} if( not isnull(channel) and len(trim(channel))>0, sales ) )

• ###### Re: Excluding Null Values in KPI

This will not work because, if you look at the 7th row, condition= bat for condition = hij.

So that doesn't have to be considered.

• ###### Re: Excluding Null Values in KPI

Hi Satish;

you may use below code for the total sum;

if(len(trim(Channel))>0, Sum(sales))

regards;

OY

• ###### Re: Excluding Null Values in KPI

For true nulls in Channel, you can try this

RangeSum(

Sum({<condition={'bat'}, Channel = {'xyz'}>}sales),

Sum({<condition={'ball'}, Channel -= {'xyz'}>}sales))

• ###### Re: Excluding Null Values in KPI

I guess this is a Qlik Sense question, but it should work the same way in QlikView and Qlik Sense

Here I forced Channel to be true null in the script... you may not need to do this...

Table:

If(Len(Trim(Channel)) > 0, Channel) as Channel,

sales,

condition;

Billing No,  Channel, sales, condition

858, xyz, 354, bat

123, xyz, 4235, bat

455, xyz, 54356, bat

788, abc, 5567, ball

554, abc, 224, ball

987, def, 55566, ball

457, hij, 677, bat

475, klm, 65545, ball

356, , 776, ball

289, , 446, ball

357, , 665, ball

];

• ###### Re: Excluding Null Values in KPI

Hi Sunny,

Just wanted to know what is the difference between My Expression and Yours.?

• ###### Re: Excluding Null Values in KPI

You expression is using an outer if statement which would need Channel as a dimension... You can make it work using an Aggr() function... but why make the expression more complicated? Why not use set analysis which will make the expression give better performance

• ###### Re: Excluding Null Values in KPI

Thanks a lot Sunny.

works as I wanted it to.

Cheers Mate !!!!!!

• ###### Re: Excluding Null Values in KPI

let's try this:

Sum( {

< channel = {'xyz'}, condition={'bat'} >

+

< channel -= {'xyz'}, condition={'ball'} >

}

if( not isnull(channel) and len(trim(channel))>0, sales )

)