Filter a Column based on Month/Quarter evaluation
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
GR
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Date Time
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @grromerro - would you be able to attach the dataset please?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
