Attendance Year Over Year

[#1]

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)

[sunny_talwar]

Seeing few issues:

1) I don't see program_id dimension

2) are you sure about this -> program_number1 = Above(program_number)

May be it would be easier to help if you can provide expected output from the sample provided.

Best,

Sunny

[#3]

Hi Sunny..

You had helped me a couple of weeks ago on this...  Below is the equation that you had given me

Aggr(If(Year>=Year1 and Year1 = Above(Year), Count({<Attendance = {'Y'}, Attendance1={'Y'}>}user_id)),Year1, Year)

My column headings have changed as I am trying to use our database data. I am not 100% sure on any of this regarding your question #2 above. I substituted program_number and program_number1 for year above.  Previously, we were checking the attendance field to see if the participant was enrolled in the program. On the new spreadsheet, all the participants were enrolled in the programs.

I am sorry should have been program_number as dimension or if possible program_code as dimension.

I hope you have a better understanding of what I am trying to accomplish...

I am trying to create a bar graph. Similar to this thread:

https://community.qlik.com/message/1046639?et=watches.email.thread#1046639

Thanks

David

[#4]

Sunny...

Can you please help me with this??

I have tried for the past few days to get this working to no avail...

I think it is similar to what you helped me with before...

Please let me know if you have any questions...

Thanks

David

[sunny_talwar]

Would you be able to show me how you would want to see this outside of QlikView? May be in Excel?

[#6]

This is what I am looking for... The 2014 bar is for all subjects who were enrolled in the 2011 and 2014 program.  The 2015 bar will be for all those enrolled in the 2014 and 2015 program.

I am using QlikSense...

Thanks

David

[sunny_talwar]

I get it know, what I am still confused about is this: user_id = Above(user_id)

Why are you checking if user_id = Above(user_id). The original expression only checked for year, I think adding user_id might be the reason that the expression might not be working.

Have you tried this:

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

[#8]

Hi Sunny,

Thanks for the quick response.... The user_id = Above(user_id) is to confirm that it was the same user in 2011 program and 2014 program, as well the same user in 2014 and 2015.  This is in place of checking the attendance flag in both years, the way it was previously.

Please confirm that you understand what I am trying to do.

Thanks

David

[#9]

Sunny,

Did my last response help clarify why I need to use the user_id field?

Thanks

David

[sunny_talwar]

It did, I just need to spend some time on this. Please give me some time (may be over the weekend), if no body else responds back, I promise I will