8 Replies Latest reply: Jan 4, 2017 12:03 PM by Jim Sullivan RSS

    Comparing Cohort datasets across a standard manufactured time dimension

    Jim Sullivan

      I've created a cohort report that tracks performance of each cohort population across calendar months (DIM).  My Selected Cohort time frame is based on a dynamic range selected by the user (2 manufactured DIM pick lists), limited to a Month-Year combination.  In this example, Jan-2015-Mar-2015.  Using variables, I then calculate the previous and future cohorts populations based on start/end of the selected cohort and number of months in that selected range.  In this example, Cohort-1 is manufactured to be Oct-2014 thru Dec-2014 and Cohort+1 is Apr-2015 thru Jun-2015.

       

      As seen below, Cohort-1 has 26 time based metrics, Cohort has 23, and Cohort+1 has 20.

       

      I'd like to time shift the graphic to a generic time definition of Month1, Month2, Month3 etc for each cohort.  If I can make this happen, the user can then see their performance by cohort population against all 3 cohorts without the need to visually time-shift each line to the left. 


      The desired view is the snapshot below but left justified across a generic Month1, Month2, Month3... axis and a value for all 3 cohorts in the first manufactured dim of Month1 thru Month20, then it would be 2 populations for another 3 months, and lastly a single population for the remaining 3 months (Month26).

       

      Thoughts on how I might make this happen would be greatly appreciated


      2017-01-03_14-12-51.png

       

      My Set Expression for Cohort:

      // Count of completed metrics

      Count(

      {<

      Eligibility = {'E'},

      [Met Status] = {'M'},

      [Report Date] = {"$(='>=' & Date($(vCS),'YYYY-MM'))"},

      [Assessment Date] = {">=$(=$(vCS)) <=$(=$(vCE))"}

      >}

      MET_CNT)

       

      /

       

      // Count of total possible metrics to be completed

      Count(

      {<

      Eligibility = {'E'},

      [Report Date] = {"$(='>=' & Date($(vCS),'YYYY-MM'))"},

      [Assessment Date] = {">=$(=$(vCS)) <=$(=$(vCE))"}

      >}

      CNT)

       

       

      //$(vCS) Cohort Start variable based on picklist value chosen by user

      //$(vCE) Cohort End variable based on picklist value chosen by user

       

       

      //[Report Date] is current DIM used to render x-axis

       

       

      //[Assessment Date] is used to ID the cohort population in conjunction with the selected cohort range

       

      Attaching limited data App