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

# Fundamental Question About Co-horts Using Sets

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!

• ###### 1. Re: Fundamental Question About Co-horts Using Sets

John,

I hope I understood this correctly maybe you could just create a LOD calc like this?

Basically at a fixed level of insured and year, if the count of new or renewal is 1 then count or don't. See sheets 3 and 4 in the attached.

• ###### 2. Re: Fundamental Question About Co-horts Using Sets

Thanks Pooja,

I need more than just a number or count as I need the specific Insured Members and associated Premium as defined by a Set to use in cohort analysis to compare this group (set) across any year.

• ###### 3. Re: Fundamental Question About Co-horts Using Sets

Hi John,

You can still use the result of the LOD calc to further analyze. You will slightly need to change the calcs depending on vizLOD if you are using other LOD calcs like include/exclude. Although, I tried this with sets too and it seems to be working fine, except the way I did it with sets wouldn't be dynamic (I think). Because I brought the fields on to the view and then created a set, so if something was to change they will need to be recreated which is why I think LOD would work better.

• ###### 4. Re: Fundamental Question About Co-horts Using Sets

Thanks Pooja.

Yes the method of Sets needs to be formula based and thus dynamic, rather than a fixed member set.

Can you show me using the LOD method in the attached modified workbook on 2nd tab how I can show the bar in 2014 represent the premium in 2014 for the Insured members identified in calculation 1 for 2015?  It should be only 1 insured member of L and the premium for that member is 140 in 2014.  This would be the cohort portion of 2014 premium.  I realize I have new or renewal on the color shelf and the premium is NOT new for this cohort in 2014, but what logic would I use to represent it on the bars?

Thanks.