Hi,
I dont have the week, I only have business date.
How can I sum each business date for a given week? I need every monday.
expected output -
date | Weekend | placeholder | newly added ---> | new placeholder | week | |
1/2/2023 | Monday | 1 | 5 | 1/2/2023 | ||
1/3/2023 | Tuesday | 1 | 5 | 1/2/2023 | ||
1/4/2023 | Wednesday | 1 | 5 | 1/2/2023 | ||
1/5/2023 | Thursday | 1 | 5 | 1/2/2023 | ||
1/6/2023 | Friday | 1 | 5 | 1/2/2023 | ||
1/9/2023 | Monday | 0 | 0 | 1/9/2023 | ||
1/10/2023 | Tuesday | 0 | 0 | 1/9/2023 | ||
1/11/2023 | Wednesday | 0 | 0 | 1/9/2023 | ||
1/12/2023 | Thursday | 0 | 0 | 1/9/2023 | ||
1/13/2023 | Friday | 0 | 0 | 1/9/2023 |
Solved! Go to Solution.
You should convert your [date] to a Date data type for it to work properly though. There is a datetime tool that can easily do that for you. Requires a bit of renaming field and removing old field, but it is the simplest way to convert 1 date field.
@wonka1234 - see attached and this datetime function page https://help.alteryx.com/20231/designer/datetime-functions
the formula tool I put in there breaks up the formula above to explain it a bit too (for future reference)
The formula is basically 2 parts:
1.) tonumber(datetimeformat([New Date Field],"%u")) which gives you the numerical representation of day of the week (Monday = 1)
2.) datetimeadd([New Date Field],-('Part 1 is in here' -1),"days") which takes the number calculated in part 1 and does the math to subtracts the # of days away from Monday that you are (so the above example is this past Monday where it becomes 0 and leaves the date as it since it is already a Monday.
I'd convert it to string with a select tool then run a formula on it replacing "-" with "_".
Replace([DateFieldName],"-","_")
I also noticed I had an error in the first pass when it was assessing it as date/time not just date. I changed "Week" formula to include a wrap of "ToDate" to clean it up. I fixed that and added the above to the workflow which is attached again. Hope this helps.
@smoosh testing this now. Why did you filter the date for less then today?