Filtering Dates
- 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
Hi,
How do I filter columns based on today's date. My input data changes daily
Eg : input
Number | 2021-05-12 | 2021-05-13 |
Sam | 300 | 400 |
Output
Name | 2021-05-12 |
Sam | 300 |
Is this possible?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Use "DYNAMIC SELECT" tool.
Configure it to "Select via a Formula".
In the Formula Pane, use [Name] = Datetimeformat(Datetimenow(),"%Y-%m-%d").
This should resolve your query.
Regards,
Shreyansh Rathod
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Also, if the data size is not so huge, you can TRANSPOSE the date columns, FILTER out today's date and then CROSSTAB the result.
This would work too.
Regards,
Shreyansh Rathod
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@shreyanshrathod this works but the Name column is missing only the date column outputs - for the dynamic select option
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
transpose date information - then use a filter with datetimetoday() set as the filter (for today) or dateatimeadd(datetimetoday(),-1,"days") for yesterday.
I'm going to point out that there is no reason to mess with dynamic select for this. this is a standard transpose/filter.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can always include more field names using OR condition.
E.g. [Name] = Datetimeformat(Datetimenow(),"%Y-%m-%d) OR [Name] = Name.
Regards,
Shreyansh Rathod
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @clarrock
Using transpose tool and filtering out would a safe bet since it can accommodate more key columns easily.
Input:
Workflow & Output :
As you can see in the above example there is 2 key columns it could n columns in real scenario. Transpose and tool can easily accommodate more key columns.
Then filter name to keep only current date and crosstab (make sure set the same key columns here) it back and it will give you only the current data columns. Refer the output in the above snapshot.
Hope this helps 🙂 Feel free to reach out if you have any questions.