Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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})
Try this
Max({<lot = {"=[Creation_Date__ILCRDJ]<Actual_Ship_Date__SDADDJ"}>} Creation_Date__ILCRDJ)
Actually, try this
Max(If(receipt < [actual ship date], receipt))
Brilliant! Worked perfectly! Thank you so much