Excluding Null Values in KPI

[satish25]

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.

Thanks in Advance.

[agigliotti]

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]

Hi Satish;

you may use below code for the total sum;

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

regards;

OY

[satish25]

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 · Accepted solution]

For true nulls in Channel, you can try this

RangeSum(

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

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

[agigliotti]

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

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]

Thanks a lot Sunny.

works as I wanted it to.

Cheers Mate !!!!!!

[satish25]

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