18 Replies Latest reply: Jun 17, 2016 5:47 PM by Sunny Talwar RSS

    Attendance Year Over Year

    David Weiser

      I would like to calculate the number of participants that were in the program from 2011 to 2014 and from 2014 to 2015. I have attached a spreadsheet below. Column program_number contains 1,2,3 where 1 represents 2011, 2 represents 2014 and 3 represents 2015.

      Each user_id is unique, unless the same user was in multiple programs.

       

      Simply... I am trying to create a cohort, represented by a bar graph

       

      [Table1]:

       

      LOAD

          user_id,

          program_code,

          born_on,

          gender,

          bp_dias,

          bp_sys,

          height,

          pulse,

          waist,

          weight,

          fbs,

          glu,

          hdl,

          he,

          ldl,

          nfbs,

          triglycerides,

          program_number

      FROM [lib://DataFiles (win-cs6kr6sd3ts_administrator)/HuntingtonYoY_20160607.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

      [Table2]:

      Load

          user_id as user_id1,

          program_code as program_code1,

          born_on as born_on1,

          gender as gender1,

          bp_dias as bp_dias1,

          bp_sys as bp_sys1,

          height as height1,

          pulse as pulse1,

          waist as waist1,

          weight as weight1,

          fbs as fbs1,

          glu as glu1,

          hdl as hdl1,

          he as he1,

          ldl as ldl1,

          nfbs as nfbs1,

          triglycerides as triglycrerides1,

          program_number as program_number1

      Resident [Table1];

       

      When I use the below statement and have program_id as the dimension, the bar graph is blank.

       

      Measure =

      Aggr(If(program_number>=program_number1 and program_number1 = Above(program_number) and user_id = Above(user_id), Count(user_id),program_number1, program_number)