Sum random multiple columns for every row

[ramii]

Hi,

    I have data from all different weeks of the month. Attached is a screenshot of the format. I want to sum the columns from sunday to saturday and store the summation in total column. However, the totals for first and last week of the month dont contain the summation from sunday to saturday (usually not complete weeks).

For example,

Week1 of June starts on 1st June (Thursday) and ends on 3rd June (Saturday) so total = Thursday +Friday +Saturday 

Week2 of June starts on 4th June (Sunday) so total = Sunday + Monday + Tuesday + Wednesday+ Thursday +Friday +Saturday 

Usually Week 2,3,4 dont give any problem.

Week5 of June starts on 25th (Sunday) but ends on 30th (Friday) so total = Sunday + Monday + Tuesday + Wednesday+ Thursday +Friday

I have to do the summations for any random month. looking for a logic or a tool which is flexible enough to do this.

[Philip]

Hi @ramii

Does this solution do what you want? It takes the date and breaks it into a year field, a month field, and a week field using the Formula Tool, then sums up the data grouped by the dates using the Summarize Tool. The sort just puts the summaries into date order.

For the Formula Tool:

Year = DateTimeFormat([Date], "%Y")

Month = DateTimeFormat([Date], "%B")

Week = DateTimeFormat([Date], "%U")

[Philip]

Sorry, missed the part about the data being in week day named fields. Fixed it with a transpose and filling dates with another Formula Tool.

[ramii]

@philip

The summation is row by row. So total for each row contains the summation of values from columns Sunday to saturday (depending on how many days were a part of the week - as described in the problem). the total column is the total of values from sunday to saturday. Please see screenshots for clarity. the highlighted part tells what days I can consider depending on what week of the month it is. 

I think the word summation caused the problem. I dont think I want a summarization tool.

Example, AlteryxWeek5 tells that saturday had a value 0.5 but it is not added to get the total. Because Saturday falls on July1st and not June30th. 

[Philip]

So I can understand, can you provide a sample input data set and an expected output data set in Excel or CSV format?

[ramii]

Hi,

   Please see the input and output files. Also check the output file for comments. The total column has different excel SUM formula for each week. I want to automate this process so no matter which month I am in, I am able to find the first and last week and then change the sum formula for those weeks.

[Philip · Accepted solution]

Here is the solution I came up with. See if this functions like you want.

The key is the second line that finds the weekday from the week_start_date then filters out any days that doesn't fall into the month.

[ramii]

Thanks a lot for your help @philip.  This is very near to what I wanted to achieve. I just had to come up with a logic to filter out the days of the last week in the month and I was done.