Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

output.JPG

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.

Thanks in Advance.

Tags (3)
1 Solution

Accepted Solutions
MVP
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
agigliotti
Honored Contributor II

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

onur0707
New Contributor III

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

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

agigliotti
Honored Contributor II

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

Capture.PNG

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

Table:

LOAD [Billing No],

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

sales,

condition;

LOAD * INLINE [

    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

];

satish25
New Contributor III

Re: Excluding Null Values in KPI

Thanks a lot Sunny.

works as I wanted it to.

Cheers Mate !!!!!!

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

Community Browser