Doing a Maximum “horizontally” as opposed to “vertically”

Thursday 14 September 2017 @ 6:17 pm

Most of the time when you see the Maximum () function in a formula, it is to calculate the highest value in a column. It could look like this:

Maximum ( {Svc.LabDate} )

which would calculate the latest lab date in the entire column. Or it could look like this:

Maximum ( {Svc.LabDate} , {Patient.ID) )

which would calulate the latest lab date for each patient’s group of records.

In both cases the Maximum is looking up and down a single column. But there is also a way to use the Maximum() function (and other summary functions) looking across several values in the same row. For example, say that your table had 4 different date fields and you wanted to find the latest date of each row. You could write a formula like this:


This would return the date value that was the greatest (latest) out of the 4 fields. Notice that you need both square brackets around the list of dates (the ‘array’) and then you need parentheses around the square brackets for the function to work. This syntax also works for Minimum, Count, Average, Sum, and several other summary functions. To see the complete list of functions that can be used this way, go into the formula editor and open the function list node labeled ARRAY.

One other note. When you write a formula that use a vertical summary function you can no longer total that field with a vertical summary operation. But if your formula is doing a horizontal summary like the last example above, that formula can still be used in a summary operation.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro