Qlik Sense - How do you compare current row to previous row in same column

[#1]

I want to track subjects that have been enrolled in concurrent years to create a cohort.  I have data for 2011, 2014, and 2015. 

I want to compare my current row (attendance) to previous row using a count if statement. This will become clearer when you view the attached spreadsheet.

My column headers are User Id, Year and Attendance.

I am trying to use the statement:

Count (If(Attendance='Y' and Above(Attendance) = 'Y',1,0))

I am getting a syntax error using "Above".

Thanks

David

[sunny_talwar]

David - Did we not resolve this here -> Year Over Year Change -- Qlik Sense

[#3]

All,

The issue we resolved was the change in enrollment, which is what is the difference in enrollment from 2011, 2014 and 2015.

I want to now find out the number of people that were enrolled in both 2011 and 2014, then those that were enrolled in 2014 and 2015.  I am looking to create a cohort.  So I would need to check for a 'Y' in attendance column for 2011 and 2014, if either one of them are 'N' then they would not be counted.  The same goes for 2014 and 2015.

I tried using the Above function with Count If but I can not get it to work.

I used the following expression:

If((Attendance = 'Y' and Above(Attendance = 'Y')), Count (Attendance))

however it did nothing on the bar chart..

Please let me know if you know how to do this...

Thanks

[sunny_talwar]

Something like this?

Not able to confirm the numbers, but if this is what you want, I have used the following script:

Table:

LOAD

    user_id,

    "Year",

    Attendance

FROM [lib://Community/SampleYoYEnrollmentOnly.xls]

(biff, embedded labels, table is Sheet1$);

Table1:

LOAD Year as Year1,

    Attendance as Attendance1,

    user_id as user_id

Resident Table;

Pivot table with

Dimension

Year

Year1

Expression

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

[#5]

I am trying what you suggested.... The code I typed is below with error message...

LOAD

    user_id,

    "Year",

    Attendance

FROM [lib://DataFiles (win-cs6kr6sd3ts_administrator)/HuntingtonYoYEnrollmentOnly.xls]

(biff, embedded labels, table is Sheet1$);

Table1:

LOAD "Year" as Year1,

    Attendance as Attendance1,

    user_id as user_id

Resident Table;

Started loading data

Sheet1$ Lines fetched: 8,790

The following error occurred:

Table not found

The error occurred here:

Table1: LOAD "Year" as Year1, Attendance as Attendance1, user_id as user_id Resident Table

Data has not been loaded. Please correct the error and try loading again.

I got same error with "" around year and without...

Do you have any ideas what I did wrong?

What do you mean by pivot table with dimension

Year

Year1?

Thank you very much for all of your help...

[sunny_talwar]

I think you missed the table name here:

Table:

LOAD

    user_id,

    "Year",

    Attendance

FROM [lib://DataFiles (win-cs6kr6sd3ts_administrator)/HuntingtonYoYEnrollmentOnly.xls]

(biff, embedded labels, table is Sheet1$);

Table1:

LOAD "Year" as Year1,

    Attendance as Attendance1,

    user_id as user_id

Resident Table;

[#7]

That fixed the problem and I was able to load the file...

I tried to create bar graph using:

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

but when I apply it I get a blank bar graph, nothing show up.  I am using year as my dimension and that equation as the measure.

I am brand new to this.... I appreciate all of your help...

Thanks for the quick response.

[sunny_talwar]

Bar graph? where did that come from? I thought you needed a pivot table, if not then we might need to change the solution a little bit

[#9]

Nope... I need a bar graph showing number of users that were enrolled in both 2011 and 2014, and enrolled in 2014 and 2015.

Example

I need to look at user_id 123 does this user have a Y  for 2011 and a Y for 2014 attendance then I want to add 1 to count. If this same user has a Y for 2011 and N for 2014 then I want to skip. I need to do the same for 2014 and 2015.

I want to create a bar graph showing number of users enrolled in 2011 - 2014 , 2014 -2015.

I hope this helps.

Thanks

[sunny_talwar]

May be using this.

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

There might be a better way to do this, but this is what I was able to get right now