Filter a Column based on Month/Quarter evaluation

Hello All,

I am trying to make a formula to filter by quarter and update a price based on the evaluation of the current month. The process will run monthly, but prices will be updated for certain products only at the beginning of each quarter. Please see the attached workflow for details and please advise if there is a better way to do it.

Thank you!

Question Quarterly Update.yxmd

Date Time

Common Use Cases

Preparation

Accepted answers

johnyli168

Hey @grromerro - would you be able to attach the dataset please?

KilianL

Hi @grromerro,

looking at your workflow, you are not filtering much.

Some tips:

You are using the following If statement:

IF [Date]="Jan" THEN "Update" ELSEIF [Date]="Apr" THEN "Update" ELSEIF [Date]="Jul" THEN "Update" ELSEIF [Date]="Oct" THEN "Update" Else "Not Update" ENDIF

You can also write it as:

If [Date] in ('Jan','Apr','Jul','Oct')
Then 'Update'
Else 'Not Update'
Endif

Also, you are filtering on the [Date], which is equal to DateTimeToday - I guess you want to filter on a date coming from the Excel Sheet instead and compare it to this value.

You could first assign quarters to the incoming dates - similar to the if statement above. If [Date from Excel Sheet] in ('Jan','Feb,'Mar') then 'Q1' elseif ...

Then you can filter for the current quarter. You can get the current quarter by using the same expression as above just for the DateTimeNow.

Let me know if this makes sense to you.

Happy solving

Kilian

Solutions Engineer - Alteryx

All comments

johnyli168

Hey @grromerro - would you be able to attach the dataset please?

KilianL

Hi @grromerro,

looking at your workflow, you are not filtering much.

Some tips:

You are using the following If statement:

IF [Date]="Jan" THEN "Update" ELSEIF [Date]="Apr" THEN "Update" ELSEIF [Date]="Jul" THEN "Update" ELSEIF [Date]="Oct" THEN "Update" Else "Not Update" ENDIF

You can also write it as:

If [Date] in ('Jan','Apr','Jul','Oct')
Then 'Update'
Else 'Not Update'
Endif

Also, you are filtering on the [Date], which is equal to DateTimeToday - I guess you want to filter on a date coming from the Excel Sheet instead and compare it to this value.

You could first assign quarters to the incoming dates - similar to the if statement above. If [Date from Excel Sheet] in ('Jan','Feb,'Mar') then 'Q1' elseif ...

Then you can filter for the current quarter. You can get the current quarter by using the same expression as above just for the DateTimeNow.

Let me know if this makes sense to you.

Happy solving

Kilian

Solutions Engineer - Alteryx

grromerro

Thank you so much for your time answering. I've added the workflow again with text input data so you can see it better.

I wonder if there is a tool that evaluates the Quarter w/o creating so many steps. I am filtering on the [Date], equal to DateTimeToday because there is no date in my data that indicate the date of the update. If I update prices today, and it happens to be April, then I have to update those specific products. A colleague suggested using filter with quarters but I don't know how to use it.

Thank you!

Question Quarterly Update.yxmd

KilianL

@grromerro , can you give some context of what you want to achieve in which case? Looking closer at your workflow, you are checking if the month is at the start of a quarter (Jan, Feb, Jul, Oct) at the time of running the workflow. What follows if this is the case and what if not? Do you have something like a process flow diagram for it?

e.g. IF first month of a quarter THEN apply these steps to the records from last quarter

grromerro

Thank you! This portion is part of a big process and the data base is huge. The purpose of the entire workflow is to create the loader for a product-price update.

The filter output is connected to a join tool that will assign the new price for the next quarter depending on the evaluation made in this tiny portion: if it says update, then the price for that couple of product will be updated, otherwise will remain the same until the end of the quarter.

This solution is working fine for the purpose, but I am a beginner and I want to know if there is a better and maybe more simple way to accomplish this results (maybe using fewer or another tools).

Thank you!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335