New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor III

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.

Tags (3)
1 Solution

Accepted Solutions MVP

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))

9 Replies Partner

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 ) )

Contributor

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

New Contributor III

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. MVP

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)) Partner

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 )

) MVP

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

];

New Contributor III

Re: Excluding Null Values in KPI

Thanks a lot Sunny.

works as I wanted it to.

Cheers Mate !!!!!!

New Contributor III

Re: Excluding Null Values in KPI

Hi Sunny,

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

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