Formatting Aggregate Values

I have a date field, with 21 that match the aggragate QUERYFORMULA in which I was seeking the starting and ending dates. I determined that in this case they are 7/1/21 and 1/11/22. One Aggregate formula is for “min”, the other is for “max” and they correctly return 2459397 and 2459591.

aggregate({FilledDate}, “min”, {MemberID=««MemberID»»},”Rx History”,true())+" to "+aggregate({FilledDate}, “max”, {MemberID=««MemberID»»},"Rx History”,true())

But I want them displayed as readable dates so I adjusted the formula to include a date pattern.

aggregate({datepattern(FilledDate,”mm/dd/yy”)}, “min”, {MemberID=««MemberID»»},”Rx History”,true())+" to "+aggregate({datepattern(FilledDate,”mm/dd/yy”)}, “max”, {MemberID=««MemberID»»},"Rx History”,true())

That caused them to display as 1/11/22 and 9/24/21. Huh?

Eventually I found that I had to embed the aggregate function inside the datepattern to get the desired output.

datepattern(aggregate({FilledDate}, “min”, {MemberID=««MemberID»»},“Rx History”,true()),“mm/dd/yy”)+" to "+datepattern(aggregate({FilledDate}, “max”, {MemberID=««MemberID»»},“Rx History”,true()),“mm/dd/yy”)

I can now see and understand why this format is necessary, but it wasn’t obvious. The minimum, for instance, needs to be calculated on the date, not the DatePattern. Since believable dates were actually being displayed though, I had overlooked the fact that they were off from what they should have been. I have a handful of similar errors to track down and correct.

Hopefully this will be a useful note to others, and my future self, about the need to do the formatting after getting values via aggregate.

The aggregate( function will work with text values. The problem, however, is that as a text value, 1/11/22 is less than 9/24/21 (because the character 1 is less than 9). So the function was doing exactly what you asked it to. Programming languages are persnickity about doing exactly what you tell them to, which is not necessarily what you actually ask them to.

In particular, when you are doing calculations or comparisons on dates, you definitely need to defer converting to text until all calculations/comparisons have been completed. This applies for numeric values as well. The number 9 is less than 27, but the text 9 is greater than the text 27.