I have a table of education deadlines (EID,EDATE), table of inspections (TID, TDATE) for a number of companies (CID) and an interval of dates, defined by 2 variables. I need to show in a single chart (straight table) :
1. CIDs.
2. The number of EIDs for each CID that fit the interval.
3. The earliest date of the inspection for each CID (TDATE), that also is between the period boundaries.
4. The number of EIDs that fit the interval AND whose dates (EDATE) are earlier than the earliest inspection date (item #3).
I assume that i.1 is a dimension. i.2-3 are also OK, but I can't find out, how I can handle i.4. I've tried to make it using aggr and count(if), but no success. Any help greatly appreciated!