I really hope this is easier than I'm making it out to be. Using a straight table I want to calculate the max date that occurs before the ship date. This is what my data looks like: This is just an example, my data is actually coming in from a SQL query.
| lot | receipt | actual ship date |
| a | 8/1/2018 | 8/4/2018 |
| a | 8/2/2018 | 8/4/2018 |
| a | 8/3/2018 | 8/4/2018 |
| a | 8/4/2018 | 8/4/2018 |
| a | 8/5/2018 | 8/4/2018 |
| b | 5/6/2018 | 8/1/2018 |
| b | 8/2/2018 | 8/1/2018 |
| c | 8/3/2018 | 8/12/2018 |
| d | 8/4/2018 | 8/13/2018 |
| e | 8/5/2018 | 8/14/2018 |
| f | 8/6/2018 | 8/15/2018 |
| g | 8/7/2018 | 8/16/2018 |
| g | 8/8/2018 | 8/17/2018 |
| g | 8/9/2018 | 8/18/2018 |
| g | 8/10/2018 | 8/19/2018 |
and this is what i want it to return.
| lot | last receipt |
| a | 8/5/2018 |
| b | 5/6/2018 |
| c | 8/3/2018 |
| d | 8/4/2018 |
| e | 8/5/2018 |
| f | 8/6/2018 |
| g | 8/10/2019 |
this is the expression I am using but I keep getting errors.
Max ({$<[Creation_Date__ILCRDJ]<Actual_Ship_Date__SDADDJ> Creation_Date__ILCRDJ})
