Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
satish25
Contributor III
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.

1 Solution

Accepted Solutions
sunny_talwar

For true nulls in Channel, you can try this

RangeSum(

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

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

View solution in original post

9 Replies
agigliotti
Partner - Champion
Partner - Champion

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

L_Hop
Creator
Creator

Hi Satish;

you may use below code for the total sum;

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

regards;

OY

satish25
Contributor III
Contributor III
Author

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.

sunny_talwar

For true nulls in Channel, you can try this

RangeSum(

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

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

agigliotti
Partner - Champion
Partner - Champion

let's try this:

Sum( {

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

+

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

}

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

)

sunny_talwar

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
Contributor III
Contributor III
Author

Thanks a lot Sunny.

works as I wanted it to.

Cheers Mate !!!!!!

satish25
Contributor III
Contributor III
Author

Hi Sunny,

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

sunny_talwar

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