Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Calc. 'Week Of' Date Based on Different Survey Submission Dates

Highlighted
5 - Atom

Hello,

 

I'm looking to calculate a 'Week of' or 'Week Ending' date based on the completion dates from a Microsoft Forms survey. The survey is completed on a weekly basis but on different days. I want to group those weekly responses into one 'Week of' or 'Week Ending' category for purposes of summarizing the data.

 

For example, our most recent survey was completed for the week ending 4/10. People completed the survey over the course of several days ranging from 4/10-4/14.

 

Our next survey will be for this week ending 4/17, so I want the formula to be dynamic on both a historical and go-forward basis.

 

Thanks for the help!

Nick

Highlighted
9 - Comet

Hi @Nickolas 

 

If its safe to assume that the surveys will always take place on or after the week ending date, but before the week ending date of the following week, I have a solution that should work for you.

 

I'm using a DateTime tool to create a column to identify the day of the week. I then use the Formula and Multi-Row Formula tools to populate the Week Ending date for each survey completion. 

 

Maskell_Rascal_0-1587061685287.png

 

Let me know if this works for you.

 

Thanks!

Phil

Highlighted
5 - Atom

Thanks, Phil.

 

You are correct that surveys will always start on Friday (week ending date) and responses are completed before the following week Friday/next week ending date.

 

What you shared is close and something I might be able to build upon. Unfortunately, there are weeks where we don't always have someone complete the survey on a Friday so there are gaps in the sequence using the Multi-Row Formula tool.

 

Nick

Highlighted
9 - Comet

Hi @Nickolas 

 

In that case, here is a simpler solution, albeit more complex formula. This is calculating the previous Friday for every date in the data series, unless the date is a Friday. 

Maskell_Rascal_0-1587065095540.png

 

Labels