Max Ifs

I'm able to use "maxifs" in excel but I'm not sure how to replicate that in Alteryx. I need to be able to pull the largest value in a field if it matches the same Title and is from a prior period.

I attached an excel file here. Would anyone be able to walk me through what tools would solve this?

Maxifs problem.xlsx

Common Use Cases

Accepted answers

Bren_Spill2

@ctheo - see attached a similar approach to @DataNath using the multi row tool.

Max If.yxmd

All comments

DataNath

Hey @ctheo - here's my take on this:

1) Chuck a RecordID on for final sorting

2) Parse the dates into ISO format so we can use them for sorting in Alteryx (think sorting on the format you're already using should be fine but I always like to convert just to be safe)

3) Sort rows based on the date

4) Create a Multi-Row Formula to check whether [Amount] is larger than the [Expected] value in the row above

5) Use the previous [Amount] value if so, and the [Expected] value if not

6) Re-sort and clean up

Replicate Excel MAXIFS.png

Replicate Excel MAXIFS.yxmd

Bren_Spill2

@ctheo - see attached a similar approach to @DataNath using the multi row tool.

Max If.yxmd

KGT

So you want the maximum of all rows before the current row for that title. Take a look at the attached. The Multi-row will get you your answer, but depending what you want to do with that answer, the top technique may help.

With the Multi-row, we only replace the answer if the answer on the previous row is larger than the amount. I left my first quick attempt there as well before I looked at the answer you were generating.