Print Page - Count Consecutive Days Absent

Title: Count Consecutive Days Absent
Post by: aolevano on 25 Jan 2012 10:05:09 AM

Hi All, (Using Cognos 8.4 report studio)

I'm stumped on trying to get a count of consecutive absences for a given date range. I have 3 sets of data I can use:
(A) lists all calendar dates in a year (starts count at July 1),
(B) lists possible days of attendance for person (Sat., Sun., Holidays excluded from set),
(C) lists a set of actual absent days for person

Sample Data:

(A)
CAL_DATE   DAY_NUMBER
2011-11-23    146
2011-11-24    147
2011-11-25    148
2011-11-26    149
2011-11-27    150
2011-11-28    151
2011-11-29    152
2011-11-30    153
2011-12-01    154

(B)
ValidDate (note that Thanksgiving (11/24), Sat., Sun. dates not there; not valid attendance dates)
2011-11-23
2011-11-25
2011-11-28
2011-11-29
2011-11-30
2011-12-01

(C)
AbsentDate
2011-11-23
2011-11-25
2011-11-28
2011-12-01

Results should show 3 consecutive absences for 11/23, 11/25, 11/28, and 1 consecutive absence on 12/1

I've been attempting a number of things using left outer from from (B) on (C) using running-count, running-total, etc. Can't get this to work because the count doesn't reset on a "present" day (11/29, 11/30)

Any help appreciated,
Thanks!

Title: Re: Count Consecutive Days Absent
Post by: blom0344 on 25 Jan 2012 02:38:57 PM

I'm dutch, so that's probably why I am missing your point ;), but as I read it the results you want are identical to the set of absence days? I'm sure not, but what then?

Title: Re: Count Consecutive Days Absent
Post by: aolevano on 27 Jan 2012 02:55:28 PM

The absent days are the data details; I need the aggregate count for each set of consecutive days. So this person should have a result like :

Staff Consec. Days
PersonA 3
PersonA 1

Obviously, there'd be multiple Staff members; each with the possibility of multiple sets of consecutive days (or none at all). I need the count to "reset" on a day the staff member is "present"...

Title: Re: Count Consecutive Days Absent
Post by: blom0344 on 29 Jan 2012 04:52:04 PM

Using set operators (except / union)

Set B
except
Set C

would yield (Set D):

2011-11-29
2011-11-30

Use this (D) as input for the union set with Set C
Add a dataitem 'Type' with value 'A' for set C and V for set D:

Result:

A 2011-11-23
A 2011-11-25
A 2011-11-28
V 2011-11-29
V 2011-11-30
A 2011-12-01

Title: Re: Count Consecutive Days Absent
Post by: aolevano on 02 Feb 2012 12:01:59 PM

Thanks for the idea.

..But from this result set, how do I get consec. counts to come out? Results should be two records, counts of 3 and 1.
If I group on "Type", I'd get a single count of 4...

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: aolevano on 25 Jan 2012 10:05:09 AM