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)

[#2]

Thank you for getting back to me... I was out of the office the end of lat week.  I look forward to hearing from you.

David

[#3]

Sunny,

To add some more clarification to why I am checking user_id = Above(user_id) is because each user_id no longer has all 3 years every time.

Ex: user id       program

       11111        2011

       22222       2011

       33333       2011

       33333       2014

       44444       2011

       44444       2014

       44444       2015

Thanks

David

[sunny_talwar]

So is the idea to include only those users which are available for all three years?

[#5]

Hi... Thanks for getting back to me...

No I need to use the users that have 2 contiguous years... The user would have 2011 and 2014, or 2014 and 2015 or both.

The goal is to determine the number of users in both "2011 and 2014 programs" and/or  "2014 and 2015 programs"

Thanks

David

[sunny_talwar]

Finally I managed to find enough time to work on this

Expression:

Aggr(If(program_number >= program_number1 and program_number1 = Above(program_number), Sum(Aggr(If(user_id = Above(TOTAL user_id), 1), (user_id,(Numeric)), (program_number,(Numeric)), program_number1))), program_number1, program_number)

Output:

Will have to rely on you for checking the numbers. But the logic seems to make sense. Also attaching the qvf for you to review.

Please let me know if this is what you wanted.

Also, I request you to close all your open threads by marking correct and helpful responses. By doing this you will not just be rewarding people who helped you, but also other people who had similar issues who might land on your discussions looking for answers to similar problems. This way together we will make this community a better place

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks and regards,

Sunny

[#7]

Sunny,

Thank you for getting back to me...  I will test this out later today or tomorrow and will let you know how it works...

I will close the other threads before the end of the day.

Thanks again...

David

[sunny_talwar · Accepted solution]

Sounds good... Hoping to hear back the positive news that this actually worked

[#9]

Thanks looks great!!

Now I need to understand exactly what it does....

David

[sunny_talwar]

I am out today... But will try to elaborate whenever I get a chance....

Best,

Sunny