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 |
Solved! Go to Solution.
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!
@anonymous008_G One way of doing this
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).
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())
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'))
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!
Thank You so much. Good learning for me.