Max/Min Dates in Formulas

Feature Requests
,
1

We need to be able to calculate the earliest/latest date from a set of connected records (e.g. first client visit or last client visit). Max/Min don't currently work for date fields.

2

Here is how you do it. I used the maximum to calculate the last login for a user from a set of login records connected to the user. First in the child object my case the login records object i have a field

Which is an Equation that takes the date field and one of knacks "features" is it will convert the date to Epoch time epoch is Unix time IE the number of seconds since midnight 1/1/1970... So the largest number will always be the nearest date. The settings look like this.

I then in the parent user object have a maximum field called last login date

Which is simply a maximum of the number epoch created by the equation of the date in the child object. It essentially looks through all the logins connected to the current user for the largest epoch number which will be the last login. Here is how the Maximum looks.

Hope this helps i remember trying to figure this one out. A knack employee ultimately helped me and let me in on the equation date trick. This is essentially the visual version of what 8947499407 explained

3

Hello! I'm facing the same issue. I understand the proposed workaround, but does anyone know if there is a simpler solution now? Just asking since it's been 2 years.

I'm new to the community. Do we know if knack is planning to improve on this?

Thanks!

4

For what it's worth, the "simplification" I mentioned on 2/17/17 to get down from a 4 step process down to a 3 step process seems to work only as long as you don't want to use the resulting date field in yet another equation.  I had to go back to the original 4-step solution I posted on 9/14 :(

This platform is so riddled with bugs, it's just not even funny.

5

Good to hear Mac - the more we share the better this platform gets, and I've certainly benefitted from the community myself.

Cheers!

6

I cannot tell how much i appriciate you guys posting this.  I am certain you saved me hours or days banging my head against a wall.  Brad, i used your 1,2.3 and it worked like a charm.  I needed to take a list of tasks (child table) and find the Min and Max start days and then do an equation from the Parent table.   Again thank you guys!!

7

Cheers Brad - looks like if there are no child records the field appears as null but is evaluated as 0 or 01/01/1970. I cant get a conditional to force an actual NULL so a "days difference" calculation compares to 01/01/70. I got around it by including a count of child records in the parent table and suppressing the date difference calculation if count of child records is 0.

8

Sorry Peter, I don't know what Knack would return.  Perhaps a conditional expression could force a blank or zero result in the equation field would cover it.

9

Thanks so much Brad! That worked, slight problem, if I have a parent record with no child records, but I can use a ternary to return a 0 - do you happen to know if Knack can return NULL instead?

Another weird thing is that I have a table in the same database that still works the old way of doing this (convert child date to number, parent selects max numeric date then converts to date format).

 

10

Hi Peter, funny we found that problem too and worked around it:

  1. Child - Equation field, Type = Date, Result = Date, equation uses the date.
  2. Parent - Maximum field, use defaults.
  3. Parent - Equation field, Type = Date, Result = Date, equation uses No. 2.

Then Knack recognizes No.3 properly as a date in calculations & filters, and any subsequent equations using it worked.  Of course all this would be simpler if a Maximum field could be performed on a date directly and that feature might come in time.

 

1 Like
11

I've been seeing the 01/01/1970 in my database and I am going mad trying to figure it out - thanks for that solution, there is still a problem going on though because if I try to use the max child date in a parent table formula [like days remaining as {Max Child Date} - CurrentTime() ]. I'm getting some weird numbers. 

I thought one solution was to calculate days remaining for all child records and then take the max of that field in the parent but that will not display any negative numbers. Sheesh - time for a ticket and I'll update this post on resolution.

12

Spot on Jeremy, that did the trick and we benefit from your work and the support help on this!

1. Child - Equation field, Type = Date, Result = Date, equation uses the date.

2. Parent - Maximum field, use defaults.

Thanks

13

Brad,

That sounds right to me, but I just (as in yesterday) completed a month-long support ticket with Knack (specifically Sam) to address problems where this randomly quit working (we were seeing 1/1/1970 for a lot of the results).  They are supposedly working on a fix for this.

What I learned in the process was that I was making it a bit more complicated than need be.  If you make the equation field (#1 in your example) a result type date, you can actually max/min that directly, and get the result you want in field #2 and eliminate field #3. (Don't get me started on why you can max/min an equation field that has a result type date but you can't max/min a date field directly)

Jeremy

14

Hi Jeremy, I can't get this to work and here's my steps:

  1. Child - DateNum equation field, type is Date, Date Type = Days, Result Type = Number, Equation uses just the Date
  2. Parent - MaxDateNum Field to Max is Child > DateNum
  3. Parent - MaxDate equation field, Type = Date, Date Type = Days, Result Type = Date

Am I missing something?

15

Reza,

This should work.  Make a field in child object named "Year_Numeric", whose calculated value is converted from "Year" (resulting in a numeric).

Then make a field in parent object named "Min_Year_Numeric", which is a min of Year_Numeric on the child object.

Then make a field in parent object named "Min_Year", whose calculated value is converted from "Min_Year_Numeric" (resulting in a date)

16
Thanks for this.

I think this might only work within the same Object though. I am trying to work the Child data and find the min "Year" from the Parent object.

Let me explore.

Reza


 

 

17

Reza,

The hack I was able to do was to use an equation with a numeric result whose formula was simply "date".  Then I could do a min/max on that (call the result numeric_max, say).  Then I did ANOTHER equation field in the other table whose formula was "numeric_max", and result type was a date.

Definitely a mess, but it works.

Jeremy

18
Hi - has there been an answer for this? I too needed to find out a MIN/MAX for Dates.
Thanks
Reza

 

19

Tony, can you re-add these images? The links are broken and I’d love to see how you did it!

20

Within the last week these have stopped returning dates. Anyone else having this issue?