Identify absence in 5 working days - expression

[gilldilpreet]

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 

[edwin]

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]

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]

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 

[Kushal_Chawda]

@gilldilpreet  Would you be able to share sample data?

[gilldilpreet]

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 · Accepted solution]

@gilldilpreet  try below

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

[gilldilpreet]

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: