topic Re: Identify absence in 5 working days - expression in QlikView

Identify absence in 5 working days - expression

gilldilpreet — Fri, 15 Nov 2024 23:53:58 GMT

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

Re: Identify absence in 5 working days - expression

edwin — Tue, 29 Sep 2020 13:45:56 GMT

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

Re: Identify absence in 5 working days - expression

edwin — Tue, 29 Sep 2020 14:45:40 GMT

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?

Re: Identify absence in 5 working days - expression

gilldilpreet — Wed, 30 Sep 2020 11:45:55 GMT

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

Re: Identify absence in 5 working days - expression

Kushal_Chawda — Wed, 30 Sep 2020 12:17:37 GMT

@gilldilpreet Would you be able to share sample data?

Re: Identify absence in 5 working days - expression

gilldilpreet — Fri, 02 Oct 2020 10:00:34 GMT

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

Re: Identify absence in 5 working days - expression

Kushal_Chawda — Fri, 02 Oct 2020 11:03:07 GMT

@gilldilpreet try below

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

Re: Identify absence in 5 working days - expression

gilldilpreet — Fri, 02 Oct 2020 13:18:42 GMT

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: