Sum random multiple columns for every row

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.

AlteryxDoubt.png

Date Time

Preparation

Expression

Accepted answers

Philip

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.

Week summation.png

Summarize by year month week.yxzp

All comments

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

Summarize by year month week.png

Summarize by year month week.yxmd

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.

Summarize by year month week.png

Summarize by year month week.yxmd

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.

AlteryxWeek1.png

AlteryxWeek2.png

AlteryxWeek5.png

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703