Max Date for each Month

[mikeb2]

Hello, 

I have a data set that looks like this: 

2016-01-05, 123

2016-01-15, 126

2016-01-27, 129

2016-02-04, 149

2016-02-15, 137

2016-02-26, 178

2016-03-04, 165

2016-03-12, 198

2016-03-21, 187

2016-03-29, 157

I want to take 3 records from this data set, the max value for each month: 2016-01-27, 129; 2016-02-26, 178; and 2016-03-29, 157.  The max date does not have to be the last date of the month as you can see, which makes this trickier.  Does anyone have any idea how to do this in Alteryx?  Thanks for your help!

[BenMoss · Accepted solution]

Hi Mike,

Here's how I would go about this.

Firstly I would create a Month/year field using the datetimeformat() formula.

I would then use the sumamrize tool to group by the month/year field, also returning the max date.

Sorted.

If required you could then join this back to your original stream. Take a look at the example workflow attached

[mikeb2]

Thank you for your help and quick response Ben, this is perfect!

[txjohnnypops79]

sir @BenMoss , I just want to say thanks as well. This helped guide me in the right direction for my report.

Thanks!