3 Replies Latest reply: Nov 2, 2010 1:36 PM by Miguel Angel Baeyens de Arce RSS

    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

       

       



       



        • Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression
          Miguel Angel Baeyens de Arce

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

                • Not Equal to Zero Or Grater Than 0 filter is not working in set analysis expression
                  Miguel Angel Baeyens de Arce

                  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.