Hi All,
For a certain project we defined several milestones and several deliverables. Below is some sample data:
| Del. No. | Planned delivery date | MilestoneCode | Planned finish date |
|---|
| 1.1 | 1-3-2013 | 1.1 | 1-2-2013 |
| 1.1 | 1-3-2013 | 2.1 | 1-6-2013 |
| 2.1 | 1-6-2013 | 3.1 | 1-12-2013 |
| 2.1 | 1-6-2013 | 4.2 | 1-12-2013 |
| 2.1 | 1-6-2013 | 5.1 | 1-2-2014 |
| 2.1 | 1-6-2013 | 6.1 | 1-8-2014 |
| 2.1 | 1-6-2013 | 6.2 | 1-5-2014 |
| 2.2 | 1-8-2015 | 3.1 | 1-12-2013 |
| 2.2 | 1-8-2015 | 4.2 | 1-12-2013 |
| 2.2 | 1-8-2015 | 5.1 | 1-2-2014 |
| 2.2 | 1-8-2015 | 6.1 | 1-8-2014 |
| 2.2 | 1-8-2015 | 6.2 | 1-5-2014 |
| 3.1 | 1-10-2013 | 4.2 | 1-12-2013 |
| 3.1 | 1-10-2013 | 6.1 | 1-8-2014 |
| 3.1 | 1-10-2013 | 6.2 | 1-5-2014 |
| 3.1 | 1-10-2013 | 7.1 | 1-5-2014 |
| 3.1 | 1-10-2013 | 8.1 | 1-10-2012 |
| 3.1 | 1-10-2013 | 8.2 | 1-11-2012 |
| 4.1 | 1-12-2013 | 6.1 | 1-8-2014 |
| 4.1 | 1-12-2013 | 6.2 | 1-5-2014 |
| 4.2 | 1-12-2013 | 6.1 | 1-8-2014 |
| 4.2 | 1-12-2013 | 6.2 | 1-5-2014 |
| 5.1 | 1-2-2014 | 6.1 | 1-8-2014 |
| 5.1 | 1-2-2014 | 6.2 | 1-5-2014 |
| 5.2 | 1-10-2013 | 6.1 | 1-8-2014 |
| 5.2 | 1-10-2013 | 6.2 | 1-5-2014 |
| 6.1 | 1-2-2014 | 7.1 | 1-5-2014 |
| 6.1 | 1-2-2014 | 7.2 | 1-12-2014 |
| 6.1 | 1-2-2014 | 7.3 | 1-2-2015 |
| 6.2 | 1-2-2014 | 7.1 | 1-5-2014 |
| 6.2 | 1-2-2014 | 7.2 | 1-12-2014 |
| 6.2 | 1-2-2014 | 7.3 | 1-2-2015 |
| 7.1 | 1-2-2015 | 8.1 | 1-10-2012 |
| 7.1 | 1-2-2015 | 8.2 | 1-11-2012 |
| 8.1 | 1-6-2015 | 6.1 | 1-8-2014 |
| 8.1 | 1-6-2015 | 6.2 | 1-5-2014 |
| 9.1 | 1-2-2013 | 9.1 | 1-11-2012 |
| 9.2 | 1-8-2015 | 9.1 | 1-11-2012 |
| | | |
I wanted to filter the milestonecodes for each Del. no with the lowest planned finish date that is higher than the planned delivery date.
This is the expression I used:
=if([Planned finish date] = min(TOTAL <[Del. No.]> aggr(min(if([Planned finish date] >= [Planned delivery date], [Planned finish date])), [Del. No.])), 'Y')
The expression displays a 'Y' with each milestonecode that matches the condition explained above.
Now here comes the tricky part:
I want to know which Del. no only has one MilestoneCode that matches that condition.
In the table above Del. No. 1.1 only matches MilestoneCode 2.1 based on these conditions, but Del. No. 2.1 could be matched to Milestonecode 3.1 and 4.2.
Any idea on how I can accomplish a count of the number of MilestoneCodes per Del. No. that match these conditions?
(I want to know this, so that I can give the deliverables that can be matched to multiple Milestones a different background color in a table).
I hope someone can help me out.
Regards,
Stevan
