Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative counting

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.        

Student16/12/201330/01/201406/01/2014
AttendedMissedAttendedMissedAttendedMissed
StudentA101010
StudentB010101
StudentC100101

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

1 Solution

Accepted Solutions
preminqlik
Specialist II
Specialist II

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

View solution in original post

9 Replies
Siva_Sankar
Master II
Master II

if it is a straight table, you can use accumulation option available in the Expression tabs.

Regards.

Siva

iktrayanov
Creator III
Creator III

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)

Not applicable
Author

Hi Maria,

   Please post the output what you want from above example with the data.

Regards,

Santhosh G

Not applicable
Author

Sorry none of the above have worked

Student16/12/201330/01/201406/01/2014
AttendedMissedAttendedMissedAttendedMissed
StudentA101010
StudentB010101
StudentC100101
StudentD011001
StudentE010110

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

Not applicable
Author

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;

iktrayanov
Creator III
Creator III

I think this should work. Check if your 'Suppress Missing' on the Presentation tab is checked.

iktrayanov
Creator III
Creator III

You can also try something like this

=if(IsNull( today()-(date([Last Attended Date]))),'Never Attended',today()-(date([Last Attended Date])))

preminqlik
Specialist II
Specialist II

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

Not applicable
Author

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