4 Replies Latest reply on Sep 16, 2015 8:35 AM by John Sobczak

    Fundamental Question About Co-horts Using Sets

    John Sobczak

      This should be low hanging fruit for many of you sharp folks

       

      In the attached mock workbook I'm trying to get a set of Insured for the records New or Renewal = 'New' and Effective Year  = '2015' to further use in cohort analysis.  I'm not quite able to get this as it is picking up one extra record for 2015 in which New or Renewal = "Renewal' because the Insured member 'L' has both 'New' AND 'Renewal' values in 2015.  The Premium for this record is 140 which is bringing the set premium for 2015 up to 480 (it should be 340).

       

      I'm using the below logic in defining my set but have also tried other varying combinations of sets as included in the mock workbook.

       

      sum(if [Effective Year]=2015 and [New or Renewal]='New' then 1 else 0 end)>0sum(if [Effective Year]=2015 and [New or Renewal]='New' then 1 else 0 end)>0

       

      I've seen some other posts on how to create these calculations, but admittedly since I don't fully intuitively understand the aggregated logic required for sets I am trying by example.  I'm probably one combination away from getting it right

       

      I appreciate any help that anyone can offer!