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
-
Debbie Sawyer ✭✭✭✭✭✭
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
-
Jbob ✭✭
Hello Debbie,
Appreciate your help, that worked perfectly!
Thank you!
Answers
-
Debbie Sawyer ✭✭✭✭✭✭
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
-
Jbob ✭✭
Hello Debbie,
Appreciate your help, that worked perfectly!
Thank you!
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/107898/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"