How to count the number of Mondays, Tuesdays, Wednesdays, etc left in the month
- 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,
Alteryx newbie here. I'm in need of something that will return the number of Mondays, Tuesdays, Wednesdays, etc left in the month based on a specified date.
I can see there is already a solution for this problem but I'm unable to open the workflow because I don't have the newer version (work won't update it). My version is 10.1.7.12188
Thanks in advance
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
Have you tried opening the workflow in Notepad and changing the value at the top to your version of Alteryx
<AlteryxDocument yxmdVer="2018.4">
To
<AlteryxDocument yxmdVer="10.1">
The other suggestion I can give you is to download a macro that details all the days from any given date. You will be able to extract the number of Mondays, Tuesdays etc left in a month by applying logic.
Macro can be found here.
https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Calendar-and-Date-Aggregation/ta-p/15576
Nick
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, I have and still couldn't open it.
Thanks for posting the link to the macros - tried opening one and got the same message. Unfortunately my version is older than that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
This is possible by using the following tools.
First of all you need to have a date that you want to calculate the number of days for the renaming days of that month. You then need to use a formula tool to calculate the last day of the month. Once you have this you can use a generate rows tool to create a line with thh date for all the remaining days (use tool example for help with this formula). Once you have that you can use a formula tool to extract the day itself, then simple group by on the date and count records. Screenshot below. Workflow attached, hopefully should work on your version.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
Thanks for assisting. I am stuck at the generating rows part of the process as I'm not sure which formula I need (I am a total novice at this - haven't had any training yet). I wasn't able to open your workflow due to having an older version.
I don't want to create new rows in my table so I'm not sure how I should go about it. What I need is the last column in the table below, and always for the current month:
Date | Name | Value | Day | Month End | Number of Days left in Month |
1/11/18 | Apple | 2 | Thursday | 30/11/18 | 4 |
2/11/18 | Banana | 3 | Friday | 30/11/18 | 4 |
3/11/18 | Peach | 4 | Saturday | 30/11/18 | 3 |
4/11/18 | Pear | 5 | Sunday | 30/11/18 | 3 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
A colleague was able to provide a genius solution which was to use the below formula to work out the remaining balance of days left in the month then divide it by 7 days and rounding it down.
FLOOR(DateTimeDiff([Month End],[Date],"days")/7)