Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
gilldilpreet
Contributor III
Contributor III

Identify absence in 5 working days - expression

Hi 

I am currently trying to create a table which shows students IDs of those students who have not attended in the past 5 working days. 

 

The fields I am working with are: 

- Session date (there can be multiple sessions in a day, all of these sessions would have to not be attended) 

- Attended flag ('Y' or 'N' )

- Student ID (identify the student)

 

Could I get some help with writing this expression please? 

 

Many thanks

Dilpreet 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@gilldilpreet  try below

=if(sum({$<[Session Date]={">=$(=date(max([Session Date])-5))<=$(=date(mx([Session Date])))"}, [Session Status]={'Y'}>}[AttendedNumeric])>0,'Y','N')

View solution in original post

7 Replies
edwin
Specialist III
Specialist III

here is a simple expression:

=if(today()-date(max({<Attendance={'Y'}>}Date))>5,1)

the logic is: get the last attendance (where attendance=Y), if the last attendance is over 5 days then its a valid row.  set suppress zero 

edwin
Specialist III
Specialist III

i explored the possibility of using e() function to look for students who didnt attend a session within the last 5 days.  at first what i did was 

=count({<StudentID=e({<Date={">=$(=today()-5)<=$(=today())"}, Attendance={'Y'}>} StudentID) >}Attendance)

this gave me students that had no attendance in any session.  meaning if the student attended at least one session that student will not be picked up. 

but if the question was to look for specific session that the student did not attend (regardless if he attended a diff session) this expression will not work.  my work around which i thought was not palatable for me was to create a composite field STUDENT &  '|' & SESSION as StudentSession.  the new expression now works:

=count({<StudentSession=e({<Date={">=$(=today()-5)<=$(=today())"}, Attendance={'Y'}>} StudentSession) >}Attendance)

this expression answers the question who didnt attend a specific session in the last 5 days

follow on question, is there a way to not have to create the composite field and still use e() function to solve this problem?

gilldilpreet
Contributor III
Contributor III
Author

Hi Edwin, 

Thanks for your help on this.

I copied the expression but am getting an error on Date:

gilldilpreet_0-1601466159696.png

Also, what result am I expecting to see here? I would ideally like this as a Y/N flag against a student i.e. they have attended in the past 5 days or they have not attended in the past 5 days.

Also, what does the 'Attendance' refer to at the end of the statement? 

Thanks again!

Dilpreet 

 

Kushal_Chawda

@gilldilpreet  Would you be able to share sample data?

gilldilpreet
Contributor III
Contributor III
Author

Hi Kush

I created 5 expressions to determine students who have attended in the past 5 days using the logic below:

sum({$<[Session Date]={"25/09/2020"}, [Session Status]={'Y'}>}[AttendedNumeric])  - labelled 'Attended TODAY'

sum({$<[Session Date]={"24/09/2020"}, [Session Status]={'Y'}>}[AttendedNumeric])  - labelled 'Attended TODAY-1'

sum({$<[Session Date]={"23/09/2020"}, [Session Status]={'Y'}>}[AttendedNumeric])  - labelled 'Attended TODAY-2'

sum({$<[Session Date]={"22/09/2020"}, [Session Status]={'Y'}>}[AttendedNumeric])  - labelled 'Attended TODAY-3'

sum({$<[Session Date]={"21/09/2020"}, [Session Status]={'Y'}>}[AttendedNumeric])  - labelled 'Attended TODA-4'

(The AttendedNumeric field is Attended=1 and Not attended=0).

 

I am now trying to determine if the students have attended in ANY of the past 5 days using the logic below but the expression is only returning '-' values. Do you know what the issue might be here:

if([Attended TODAY]>'0',
if([Attended TODAY-1]>'0',
if([Attended TODAY-2]>'0',
if([Attended TODAY-3]>'0',
if([Attended TODAY-4]>'0','Y','N')))))

 

Thanks 

Dilpreet 

 

Kushal_Chawda

@gilldilpreet  try below

=if(sum({$<[Session Date]={">=$(=date(max([Session Date])-5))<=$(=date(mx([Session Date])))"}, [Session Status]={'Y'}>}[AttendedNumeric])>0,'Y','N')

View solution in original post

gilldilpreet
Contributor III
Contributor III
Author

HI Kush, 

If I wanted to add the expression above as a new field in the script, how would I do that? It doesn't seem to be liking the below:

gilldilpreet_1-1601644615497.png