topic Re: Limiting column values at the maximum value in Alteryx Designer Desktop Discussions

Limiting column values at the maximum value

Anastasiia — Sun, 23 Sep 2018 20:23:34 GMT

Hello,

I have a project data and I am calculating the number of projects reaching a certain gate in a certain month. I would like to limit the data that if date is the maximum and value is the maximum per project then display max date and max value per project only once. So the rest of the fields after latest date with first max value would be removed or replaced with null. In the example, Project one would have only data until 201901 and 201902 would be removed because it is the latest date with first max value per project. How could I do it?

-Anastasiia

Re: Limiting column values at the maximum value

BenMoss — Sun, 23 Sep 2018 21:31:10 GMT

Hi!

I'm struggling to understand completely what you are looking for, so could you please post what your output data should look like.

Also, it would be great if you could provide the initial data as an excel file, just so people don't have to spend time unnecessarily replicating it.

Ben

Re: Limiting column values at the maximum value

brindhan — Mon, 24 Sep 2018 01:59:30 GMT

Hi @Anastasiia

Hope this is the result you need. Enjoy!

Cheers,

Re: Limiting column values at the maximum value

Anastasiia — Mon, 24 Sep 2018 03:59:44 GMT

@brindhan, hi!

Thank for the solution. Just what I needed. Multi-row formula is a new tool for me. Thanks for the help!

Re: Limiting column values at the maximum value

Anastasiia — Mon, 24 Sep 2018 04:07:04 GMT

@BenMoss, hi!

I was looking for a way how to limit number of rows when a project has the max value in Value column and have the latest date. In my example, I have Project 1 and two rows: 201901 Value 12 and 201902 Value 12. So I wanted to display only 201901 Value 12 and remove the dates after 201901 with max value (in this case, 201902 Value 12)

Multi-row formula presented in below solution helped to achieve result.

I have already received the solution. Thank you!

-Anastasiia