6 Replies Latest reply: Nov 15, 2016 9:44 AM by Joanna Seldon RSS

    Filter on IF expression qlik sense

    Joanna Seldon

      Hi

       

      I have a straight table chart on my sheet

       

      The table chart shows

       

      CustomerID is column 1, column 2 the next shows the following measure

       

      if(COUNT(distinct{$<MonthChart= {"$(='>=' & MonthStart(AddMonths(Max(MonthChart),-0)) & '<=' & Date(Max(EventDate), 'DD/MM/YYYY'))"}>}OrderID) >= 4, 'Yes', 'No')

       

      I just want the column to filter automatically only on CustomerID 'YES'. I can order by Yes or No. I wish I could sort by expression

       

      basically creating a list of Customers of Yes only

       

      please help

        • Re: Filter on IF expression qlik sense
          Mark Ritter

          I might suggest that you do this calculation in your load script with a new field that contains the Yes or No.

           

          Then you can either have a filter that allows the user to pick Yes or No or all.  Or you can limit what is displayed in the table with set analysis.

           

          You can sort by an expression.

            • Re: Filter on IF expression qlik sense
              Joanna Seldon

              Hi

               

              count does not work in a dataload for this example above

               

              I have tried a rank function with no joy

               

              I have also have the following

               

              If(COUNT(distinct{$<MonthChart= {"$(='>=' & MonthStart(AddMonths(Max(MonthChart),-0)) & '<=' & Date(Max(EventDate), 'DD/MM/YYYY'))"}>}OrderID) >= 4, 0)

               

              I have tried looking under the appearance tab, but I cant seem to keep just the '0' values and not show the nulls

               

              please help

            • Re: Filter on IF expression qlik sense
              Shraddha Gajare

              can you share sample data?

                • Re: Filter on IF expression qlik sense
                  Joanna Seldon

                  Hi

                   

                  I cant sorry...

                   

                  just wish there was of way of limiting the values shown in the table shown on the qlik sheet as like the following

                   

                  Currently shows

                   

                   

                  CustomerID          4> Repeat Orders in Current month

                   

                  AAAA                                0

                  BBBB                                0

                  CCCC                               0

                  DDDD                              Null

                  EEEE                               Null

                  FFFF                                0

                   

                   

                  Wish table on sheet to display as

                   

                  CustomerID          4> Repeat Orders in Current month

                   

                  AAAA                                0

                  BBBB                                0

                  CCCC                               0

                  FFFF                                 0

                   

                  please help