8 Replies Latest reply: Jan 10, 2017 12:13 PM by Sunny Talwar RSS

    Help with Concatenated value variable in Set Expression

    Jim Sullivan

      I've created a variable based on a set expression:

      vCohortMbr_test =

      Concat(
      {<
      Eligibility = {'E'},
      MEMBER_ID = {'*'},
      [Report Date] = {"$(='>=' & Date($(vCE),'YYYY-MM')) $(='<=' & Date($(vCE),'YYYY-MM'))"},
      [Assessment Date] = {">=$(=$(vCS)) <=$(=$(vCE))"}
      >}
      DISTINCT Chr(34) & MEMBER_ID & Chr(34),',')

      that generates a string of Member IDs that

      1) Have a status of Eligible

      2) MEMBER_ID must not be Null

      3) In a set reporting period (single month, last month of the cohort period based on an island calendar picklist) [Report Date]

      4) That also had a date value [Assessment Date] within the cohort reporting window which is based on vCS and vCE for the start/end date values for selected cohort timeframe.

       

      Based on that unique Member ID list of values I need to perfume some set expression, ie Count() and Sum()

       

      However, I can't seem to get the variable to work in a set expression, simple syntax error I assume?

       

      A sample Set expression I've tried looks like the below, having tested different iterations of single and double quotes and $( expansions without any luck.

       

       

      sum(
      {<
      MEMBER_ID = {'$(vCohortMbr_test)'}
      >}
      CNT)

       

      What am I doing wrong?

       

      Thks

      Jim