3 Replies Latest reply: Aug 9, 2016 5:09 AM by Sunny Talwar RSS

    Count IF distinct function.

    Sam Maile

      Hi there,

       

      trying to put together two expressions in one count 'if' statement.

       

      count (DISTINCT if((option_1 + option_2 + option_3 + option_4 + option_5)>0,INDNR))


      I need to include where other options are = 0 within the same expression to fetch the correct figure.

       

      Thanks.

        • Re: Count IF distinct function.
          Sunny Talwar

          Something like this?

           

          Count(DISTINCT {<option_1 = {'>0'}>+<option_2 = {'>0'}>+<option_3 = {'>0'}>+<option_4 = {'>0'}>+<option_5 = {'>0'}>} INDNR)

            • Re: Count IF distinct function.
              Sam Maile

              Something very much like this, need to get option 5 (for example):

              <OPTION_5  = {'0'} ?


              Would it be:

              Count(DISTINCT {<option_1 = {'>0'}>+<option_2 = {'>0'}>+<option_3 = {'>0'}>+<option_4 = {'>0'}>+<option_5 = {'0'}>} INDNR)

               

              SQL Looks like this when filtered.

               

              (OPTION_1+ OPTION_2+ OPTION_3 + OPTION_4) >0

                            AND                                                           

                         (OPTION_5 + OPTION_6 ) = 0

                     AND                                                          

                         (OPTION_7 + OPTION_8 + OPTION_9) = 0

                     AND                                                         

                         (OPTION_10 + OPTION_11 + OPTION_12) = 0      

               

              The count IF statement will need to count Option 1-5 (>0) and the other options to equal 0 (so they are not returned).