# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
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)
• ### Expression

1 Solution

Accepted Solutions

@gilldilpreet  try below

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

7 Replies
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

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?

Contributor III
Author

Hi Edwin,

Thanks for your help on this.

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

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

@gilldilpreet  Would you be able to share sample data?

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

@gilldilpreet  try below

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

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:

Community Browser