Max Ifs

[ctheo]

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?

[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

[Bren_Spill · Accepted solution]

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

[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.

[Qiu]

Multi-row tool can do it very simply, nice ones.

I took a different an so well way though. 😂

[Yoshiro_Fujimori]

@ctheo 

I am not familiar with MaxIfs() of Excel, but I tried to follow the expected output.

I used Batch Macro as your expression seems to change the reference ranges at each row.

If you are not familiar with Macro, please check the Interactive Lessons for Macro.

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

I hope this helps.

Main Workflow

Batch Macro

[ctheo]

Thanks for your help!

[ctheo]

Thanks for your help! I love the simplicity of the solution