topic Re: Cumulative counting in QlikView

Cumulative counting

Tue, 07 Jan 2014 15:38:54 GMT

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

Re: Cumulative counting

Siva_Sankar — Wed, 08 Jan 2014 05:48:12 GMT

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

Regards.

Siva

Re: Cumulative counting

iktrayanov — Wed, 08 Jan 2014 06:36:09 GMT

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)

Re: Cumulative counting

Wed, 08 Jan 2014 06:42:11 GMT

Hi Maria,

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

Regards,

Santhosh G

Re: Cumulative counting

Wed, 08 Jan 2014 10:04:24 GMT

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

Re: Cumulative counting

Wed, 08 Jan 2014 16:27:38 GMT

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;

Re: Cumulative counting

iktrayanov — Wed, 08 Jan 2014 17:25:04 GMT

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

Re: Cumulative counting

iktrayanov — Wed, 08 Jan 2014 17:40:50 GMT

You can also try something like this

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

Re: Cumulative counting

preminqlik — Wed, 08 Jan 2014 19:39:19 GMT

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

Re: Cumulative counting

Thu, 09 Jan 2014 12:38:08 GMT

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