Need Help regarding custom filter

Hi all --- I am working on one enhancement project in the Alteryx where I need to filter out data such a way where only latest quarter dates will be appeared and prior to that dates will be removed from final output. For example, in below table we have multiple dates for income and expenses, but I only wanted to filter out the records which are belongs to latest quarter. Here, 07/18/2023, 08/24/2023 and 09/26/2023 are from latest quarter.

Name	Date	Income/Expense	Values
Dan	7/18/2023	income	10000
Dan	5/18/2023	income	1200
Mike	5/24/2023	income	31000
Mike	8/24/2023	Expense	4256
Andy	5/26/2023	income	9871
Andy	9/26/2023	Expense	876
Dan	6/30/2023	Expense	4567
Gabrial	5/28/2021	income	23451
Sohan	5/28/2021	income	462427
Mike	7/22/2021	income	83745
Sohan	7/22/2021	Expense	132436
Andy	8/11/2021	Expense	98765

Help

Accepted answers

binu_acs

@anonymous008_G One way of doing this

Quarter_031023.yxmd

All comments

DataNath

Hey @anonymous008_G, I'm a little confused here. Do you mean in comparison to the latest quarter within your dataset? As we're now in Q4 and so none of the dates in your data are actually part of the current quarter. If you want current, the following filter expression ought to work:

Ceil(DateTimeMonth(DateTimeParse([Date],'%m/%d/%Y'))/3)=Ceil(DateTimeMonth(DateTimeToday())/3)
AND
DateTimeYear(DateTimeParse([Date],'%m/%d/%Y'))=DateTimeYear(DateTimeToday())

If you want the latest from your data set then here's a simple workflow that can do this!

Isolate latest Q.png

Take only latest Q dates.yxmd

binu_acs

@anonymous008_G One way of doing this

Quarter_031023.yxmd

anonymous008_G

Apologies if my description is not clear, i mean latest quarter meaning the latest quarter which is passed. here, the passed latest quarter is Q3 (Jul to Sep).

DataNath

Ah thanks for clarifying @anonymous008_G - if that's the case then you could just slightly adapt the filter condition to be this:

Ceil(DateTimeMonth(DateTimeParse([Date],'%m/%d/%Y'))/3)=Ceil(DateTimeMonth(DateTimeToday())/3)-1
AND
DateTimeYear(DateTimeParse([Date],'%m/%d/%Y'))=DateTimeYear(DateTimeToday())

anonymous008_G

Thanks both of you, i have incorporated both of this ideas in my workflow and result is as expected. Can someone from you please explain me this formula. I know one is to identify the quarter and one to identify the year. but can someone please help me to read this formula
Ceil(DateTimeMonth(DateTimeParse([Date],'%y/%m/%d'))/3)

DateTimeYear(DateTimeParse([Date],'%y/%m/%d'))

DataNath

Glad to hear it's working @anonymous008_G! And absolutely. Here we read the functions working from the inside so:

DateTimeParse([Date],'%m/%d/%Y') parses/converts your incoming date format of mm/dd/yyyy into ISO-format (YYYY-MM-DD), as this is the only format Alteryx can work with. That is then wrapped in:

DateTimeMonth(), which returns the month of a date, which we then divide by 3 to get a quarter 'value'. This is then wrapped in:

Ceil() which takes the ceiling value i.e. forces a round up. For example, 3.1 would round up to 4. This gets us the true quarter.

The second expression is the same, except the dateparse is wrapped in DateTimeYear() instead, in order to return the year. This is because we need to make sure the years are the same as well as the quarters.

Hope this helps!

anonymous008_G

Thank You so much. Good learning for me.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703