Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have built an attendance monitoring model and in one of the tables displays students attendance and missed attendance data for each week as in the table below e.g.
Student | 16/12/2013 | 30/01/2014 | 06/01/2014 | |||
---|---|---|---|---|---|---|
Attended | Missed | Attended | Missed | Attended | Missed | |
StudentA | 1 | 0 | 1 | 0 | 1 | 0 |
StudentB | 0 | 1 | 0 | 1 | 0 | 1 |
StudentC | 1 | 0 | 0 | 1 | 0 | 1 |
I am having difficulty with the following - I would also like to include an end column that displays the accumulated number of missed attendances, but this column should only count consecutive missed attendances. E.G. for StudentA in the table above this would return 0, for StudentB it would return 3, for StudentC it would return 2 and for StudentD it would return 1
I have used the following to calculate the number of attendances
=Count({<ExtractType={'1'},StudyYearCommencementDate={'>01/01/2013''<30/07/2014'},TIER4={'1'},Attended={'Attended'}>}WeekCommencing)
And the following to calculate the number of missed attendances
=Count({<ExtractType={'1'},StudyYearCommencementDate={'>01/01/2013''<30/07/2014'},TIER4={'1'},Attended={'Absent'}>}WeekCommencing)
Any advice welcome
Regards
Maria Hourigan
hi make sure your backend table in a proper way such that i attached a file , look into sheet2
and also find qvw
hope this helps you
if it is a straight table, you can use accumulation option available in the Expression tabs.
Regards.
Siva
Try this as Third expression called 'Acumulation'
=If(Count({<ExtractType={'1'},StudyYearCommencementDate={'>01/01/2013''<30/07/2014'},TIER4={'1'},Attended={'Attended'}>}WeekCommencing) >0, Count({<ExtractType={'1'},StudyYearCommencementDate={'>01/01/2013''<30/07/2014'},TIER4={'1'},Attended={'Attended'}>}WeekCommencing) + before(Count({<ExtractType={'1'},StudyYearCommencementDate={'>01/01/2013''<30/07/2014'},TIER4={'1'},Attended={'Attended'}>}WeekCommencing) ,3),0)
Hi Maria,
Please post the output what you want from above example with the data.
Regards,
Santhosh G
Sorry none of the above have worked
Student | 16/12/2013 | 30/01/2014 | 06/01/2014 | |||
---|---|---|---|---|---|---|
Attended | Missed | Attended | Missed | Attended | Missed | |
StudentA | 1 | 0 | 1 | 0 | 1 | 0 |
StudentB | 0 | 1 | 0 | 1 | 0 | 1 |
StudentC | 1 | 0 | 0 | 1 | 0 | 1 |
StudentD | 0 | 1 | 1 | 0 | 0 | 1 |
StudentE | 0 | 1 | 0 | 1 | 1 | 0 |
Please note I only want to count consecutive accumulated missed attendances (in my base data a missed attendance is identified as "Absent") so if the student did not attend for several weeks but attended this week the count would be 0 and if the student did not attend this week but attended last week the count would be 1
The output I would expect from the table above for the 06/01/2014 would be
Student Consecutive Missed Attendances
StudentA 0
StudentB 3
StudentC 2
StudentD 1
StudentE 0
Regards
Maria
I have added a resident table (see code below) that has a new field called 'Last Attended Date' I am going to
use this with today() to calculate the number of days between today and the
last attended date. However one Issue I have with this is that it excludes those
who have never attended. Any Ideas or solutions to the overall problem are more
than welcome.
LOAD TERM,
BannerID,
MAX (WeekCommencing) as 'Last Attended Date'
Resident Attendance_Data
Where (Attended = 'Attended' or Attended = 'Authorised')
Group by TERM, BannerID
;
Left join (Attendance_Data)
Load TERM,
BannerID,
'Last Attended Date'
Resident Attendance_Data_New;
I think this should work. Check if your 'Suppress Missing' on the Presentation tab is checked.
You can also try something like this
=if(IsNull( today()-(date([Last Attended Date]))),'Never Attended',today()-(date([Last Attended Date])))
hi make sure your backend table in a proper way such that i attached a file , look into sheet2
and also find qvw
hope this helps you
I went with making a few more changes in the load and it worked
However I can see the potential for this sample and will build a better
model using this method.
Thank you all for your help and guidance.
Regards
Maria