MAX function in formula not working

[DataUser]

How do you use the MAX function? I am trying to write the following formula but am getting an error saying there are not enough parameters for MAX:

IF ([Pay Period]+26) = MAX([Pay Period]) THEN [Sum_Double_Time] ELSE 0 ENDIF

In other words, I have two week pay periods for all of 2017 and 2 for 2018. I need to match the pay period from 2018 to the corresponding pay period in 2017 and return a value. I'm creating a new column with only values in my data set that match the period. How do you use the MAX function in order to do this?

Thank you

[MarqueeCrew · Accepted solution]

@DataUser,

First you will want to take the data into a SUMMARIZE tool to find the MAX value for the field.  Then you can use an APPEND fields tool (connected first to the data source and then to the summary output) to append the MAX_Field value to every record.  Now you can simply compare with logic like:

IF [Pay Period] + 26 = [Max_Pay Period] THEN [Sum_Double_Time]
ELSE 0
ENDIF

The Max() function compares values horizontally and you need to compare vertically.

Cheers,

Mark

[Stuart_Nulty]

This is a clunk of Alteryx, having to use two tools and append a new column just to get the max of a field is really messy.

You might need to find dozens of max fields at different points in the workflow. This should be handled through the formula tool.