I have every business date and a value, sum each value by week

[wonka1234]

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

[DanielG]

@wonka1234   - try this formula

datetimeadd([date],-(tonumber(datetimeformat([date],"%u"))-1),"days")

[DanielG]

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.

[RSreeSurya]

Hi @wonka1234 ,

If you could explain bit more detail, for my understanding 

[DanielG]

@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)

[smoosh · Accepted solution]

See attached workflow

First piece is to create a list of dates and weeks that go well into the future. 

Second portion is to join in your records

Third part aggregates to your expected values at the weekly view. 

[DanielG]

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.

[wonka1234]

@DanielG 

thanks but im trying to convert the date now to  this format as a a string yyyy_mm_dd 

[DanielG]

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.  

[wonka1234]

@smoosh  testing this now. Why did you filter the date for less then today?