1 Reply Latest reply: Apr 27, 2010 6:01 AM by juergm RSS

    Set analysis based on null value fields

      Hi all,

      Currently I am looking for a method to filter selections on null value fields with set analysis.

      e.g.

       


      Test:
      load
      RowNo() as ID,
      if(Product = 'C', '', if(Product = 'E', Null(), Product)) as Product,
      Sales
      inline
      [
      Product, Sales
      A, 100
      A, 200
      B, 300
      C, 400
      C, 400
      D, 500
      E, 600
      E, 700
      F, 800
      G, 900
      G, 1000
      ];


      error loading image

      Question: "What is the sum of product with NULL value (not empty)?"

      In my sample, total sum is 5900, empty sum 800, null sum 1300.

      I have tried some expressions, but all failed. Finally I got it from another post "http://community.qlik.com/forums/t/16384.aspx".

      "Sum({1-<Product>0>} Sales)" AND "Sum({1-<Product={*}>} Sales)" both work.

      I also tried some other expressions.

      error loading image

      But as I know, we normally use SA like this: "Sum({<Product={">0"}>} Sales)". Can we use the ">" or "<" directly after the filed "Product"? If the answer is "YES", why it does not work on the filter "ID>100"?

      Can anyone explain why? Thanks a lot!

        • Set analysis based on null value fields
          juergm

          Interesting, your table shows all nulls for the lower 8 formulas.
          I am using 9.00.7119.4 64 bit

          Had once a similar issue and reveived the following formula for ignoring nulls:

          Sum({<Product -= {}>} Sales)

          which shows correct as 4600

          however, using

          Sum({<Product = {}>} Sales)

          results in 0 which I can not understand.

          For your other formulas I have not seen any example where you could limit on greater than or less than and I assume it throws an error, do not know however how we can see that.

          Not much help from me but an interesting example, I hope some more comments come in