topic Re: Help with picking max. qtr. for a max. year in Alteryx Designer Desktop Discussions

Help with picking max. qtr. for a max. year

MROGK — Mon, 03 Jan 2022 20:30:56 GMT

Can someone help me how to arrive at max. quarter for a max. year. In the below table, would like to arrive at Year 2022, Qtr. 1. When I use the summarize (using group by max. year & max. qtr.), I get both 2021 Q4 & 2022 Q. Thankyou.

Team	Prod Date	Qtr.	Year
AKC	12/1/2021	4	2,021
AKC	12/2/2021	4	2,021
AKC	12/3/2021	4	2,021
AKC	12/4/2021	4	2,021
AKC	12/5/2021	4	2,021
AKC	12/1/2021	4	2,020
AKC	12/2/2021	4	2,020
AKC	12/3/2021	4	2,020
AKC	12/4/2021	4	2,020
AKC	12/5/2021	4	2,020
AKC	1/1/2022	1	2,022
AKC	1/2/2022	1	2,022
AKC	1/3/2022	1	2,022
AKC	1/4/2022	1	2,022
AKC	1/5/2022	1	2,022

Re: Help with picking max. qtr. for a max. year

apathetichell — Mon, 03 Jan 2022 20:40:02 GMT

you need to take max of prod date when prod date is a date - then convert that into a year/quarter after.

See attached for an example of how you'd do this. Note - there is some fanciness in the dynamic quarter conversion. It's a bit less intuitive.

Re: Help with picking max. qtr. for a max. year

MROGK — Mon, 03 Jan 2022 20:40:39 GMT

Just to clarify, looking for the output

Team	Qtr.	Year
AKC	1	2,022

Re: Help with picking max. qtr. for a max. year

apathetichell — Mon, 03 Jan 2022 20:47:31 GMT

so you want Team as well? You didn't ask for it. Take you datastream after the date/time tool and join it to the stream coming out of the formula tool. Join on the datetimeout and the max date field. The join anchor will be any entries matching the max date.

Re: Help with picking max. qtr. for a max. year

Qiu — Tue, 04 Jan 2022 02:53:35 GMT

@MROGK
I try to avoid the DateTime formula.

Re: Help with picking max. qtr. for a max. year

atcodedog05 — Tue, 04 Jan 2022 07:06:57 GMT

Hi @MROGK

You don't need to convert the date or use the formula tool at all you can just sort by year (descending) sort by qtr (descending) and only keep the first row.

Hope this helps : )