4 Replies Latest reply: Mar 11, 2011 6:21 AM by Jonathan Dienst RSS

    Set Analysis with subset filter

      Hi,

      I am turning to the forum as I have exhausted all my investigation on this subject. I have a requirement to count a number of distinct values where specific values in a previous piece of set analysis have occured. In essence a subset of values to be part of the where clause. Below is the code I am trying to do, I think I am missing a symbol or something but sure I am very close to the solution:

       

      =count(distinct {$<
      [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
      [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
      [Line Number] = {2},
      linkVendorInvoiceHeader =
      {"$
      (= chr(34) & concat(
      {<
      [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
      [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
      [Line Number] = {1},
      IncludedInFirstTimePass = {1}
      >} distinct linkVendorInvoiceHeader,
      chr(34) & ',' & chr(34)) & chr(34)
      "}>}

      linkVendorInvoiceHeader)


      The specific area of the subset when run seperatley works as expected, heres the code on its own:

       

      = chr(34) & concat(
      {<
      [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
      [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
      [Line Number] = {1},
      IncludedInFirstTimePass = {1}
      >} distinct linkVendorInvoiceHeader,
      chr(34) & ',' & chr(34)) & chr(34)


       

       

      This returns values like this:

       

      "a1","a2","a3","a4"
      So when I manually copy and paste the text results into the top code example the figures return. But it doesnt work dynamically. If anyone guru's out there know what is syntaxtically wrong with my code please help

      Thanks,

      Jon





        • Set Analysis with subset filter
          Miguel Angel Baeyens de Arce

          Hello Jon,

          Then you are double double quoting if I'm not wrong (remove double quotes where the bold brackets):

           


          Interserve wrote:=count(distinct {tiny_mce_markerlt;<blockquote><pre> [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
          [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
          [Line Number] = {2},
          linkVendorInvoiceHeader =
          {$
          (= chr(34) & concat(
          {<
          [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
          [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
          [Line Number] = {1},
          IncludedInFirstTimePass = {1}
          >} distinct linkVendorInvoiceHeader,
          chr(34) & ',' & chr(34)) & chr(34)
          }>}

          linkVendorInvoiceHeader)

           

          Hope that helps

            • Set Analysis with subset filter

              Hi Miguel,

              Thanks for the quick responce! Your code does not show bold but take it you mean removing the double quotes from linkVendorInvoiceHeader = {"$.

              I did that but rather than a "0" being displayed in the results I now get "-" which tells me there is a problem...

              • Set Analysis with subset filter

                Hello again Miguel,

                Ignore my last quick reply, I have success! Thanks for the prompt on the quotes. For the benefit of anyone reading this here is the solution. It was pesky brackets at fault!

                 

                =count(distinct {$<
                [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
                [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
                [Line Number] = {2},
                linkVendorInvoiceHeader = {$(= chr(34) & concat(
                {<
                [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
                [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
                [Line Number] = {1},
                IncludedInFirstTimePass = {1}
                >} distinct linkVendorInvoiceHeader,
                chr(34) & ',' & chr(34)) & chr(34))

                }>} linkVendorInvoiceHeader)