Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to count the number of Mondays, Tuesdays, Wednesdays, etc left in the month

SecretChimpanzee
7 - Meteor

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

5 REPLIES 5
NickC
Alteryx Alumni (Retired)

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

SecretChimpanzee
7 - Meteor

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.

NickC
Alteryx Alumni (Retired)

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.

 

Count number of days.png

SecretChimpanzee
7 - Meteor

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:

 

DateNameValueDayMonth EndNumber of Days left in Month
1/11/18Apple2Thursday30/11/184
2/11/18Banana3Friday30/11/184
3/11/18Peach4Saturday30/11/183
4/11/18Pear5Sunday30/11/183
SecretChimpanzee
7 - Meteor

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)

Labels