Using MAX to get latest date by criteria
Hello,
I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column.
=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)
Appreciate any suggestions!
Best Answers

HI @Jbob
Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.
In mine the column called Date and the column called Max per Value are BOTH Date data types.
I have exposed the formula I have used in the last column to show how the Max per Value is coming through.
I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?
Does this help at all?
So in yours:
=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)
Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.
Kind regards
Debbie

Hello Debbie,
Appreciate your help, that worked perfectly!
Thank you!
Answers

HI @Jbob
Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.
In mine the column called Date and the column called Max per Value are BOTH Date data types.
I have exposed the formula I have used in the last column to show how the Max per Value is coming through.
I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?
Does this help at all?
So in yours:
=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)
Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.
Kind regards
Debbie

Hello Debbie,
Appreciate your help, that worked perfectly!
Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!