Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression

Hi All,
I am creating a graph with the expression as

=count(aggr(count({$<[No Of Domains Created] -= {0}, [No Of Domains Created] > {'0'}>}[Sharing Fact Session Dim Key]), [Sharing Fact Client Dim Key]) )

but when i am trying to check the data with database I can see that the filter Not Equal to 0 is not getting applied.

Please help me in writing not equal to Zero Or Grater than zero condition.

Guide me if i am writing anything wrong in chart expression.

Its a histogram

Its bit urgent.

Thanks in Adavnce.

Regards,

Rashmi





3 Replies
MVP
MVP

Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression

Hello Rashmi,

Two quick things:

First, a simple syntax mispelling:

=count(aggr(count({$<[No Of Domains Created] -= {0}, [No Of Domains Created] ={'>0'}>}[Sharing Fact Session Dim Key]), [Sharing Fact Client Dim Key]) )


Second, the condition greater than zero implies the value is not equal to zero, so ,why don't use just

=count(aggr(count({$< [No Of Domains Created] ={'>0'}>}[Sharing Fact Session Dim Key]), [Sharing Fact Client Dim Key]) )


Hope that helps.

Not applicable

Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression

Hi Miguel,

Thanks for your quick reply, I agree that i dont need -= 0 condition when i have >0 but when i was trying to give -= 0 condition its not working. Is nything wrong in this syntax?

Is there any other way to specify -= 0 and not null conditions?

Please guide me on this.

Thanks,

Rashmi

MVP
MVP

Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression

Hello Rashmi,

Dealing with nulls is complicated in QlikView since very data source use them in a different way. Since a null value is a value that doesn't exist, usually there's no way to identify them in one chart and select all values instead of those.

Actually, if you search the Community looking for them, you will find very different solutions, some of them worked some didn't. What I do recommend you is trying to identify them in the load script, flag them to use this flag later on.

Say for example that you have one field "MonthlySalesPercentage" that may be null. You can do the following in your script:

Providers:LOAD ID, Name, MonthlySalesPercentage, If(Len(MonthlySalesPercentage) = 0, 1, 0) AS MSPFlag;SQL SELECT ID, Name, MonthlySalesPercentageFROM Database.Providers;


Then you can use that in one chart:

Count({< MSPFlag = {1} >} ID)


To show those records with a null or empty value.

In regards to your second question, -= is a valid operator and your syntax is just fine, bur redundant. -= 0 will not exclude null values, as they are not actually zero, but null.

Hope that helps.

Community Browser