Summarize data by the week

[cwarren]

I wanted to post a little shortcut we’ve been using to get from daily sales data to a weekly summary. This is a quick-and-dirty tool to summarize data by the week.

You may have sales data on the daily or individual transaction level with a date attached, or store traffic on a daily basis. When doing an analysis of time-series data (such as AB Analysis), daily data may be too dense and monthly data not dense enough. So summarizing up the week level needs to be complete before data can move downstream.

This module creates a new date field called [WeekOf] that is then used for Grouping in the Summarize tool. It’s easy enough to populate this field with the first day of the month or the first day of the quarter, but days of the week don’t line up with any particular day of the month. So in this case we use the power of fractions!

The required fields for data coming into this tool are (1) a date field and (2) a numerical field representing sales or traffic counts or whatever it is you need to group. Optional fields included in the sample data are Store Number (because sales by channel or by store is required for AB Analysis), and Sales_SetB and Sales_SetC (which could represent for example, sales from different departments, Mens, Womens, Childrens, Shoes, etc).

The Formula tool performs four sequential calculations. The first expression calculates the fraction of weeks (one day = 1/7 week) between a known date and the date in the data stream. The second expression turns this number into a string. The third expression uses an if/then statement to assign which day of the week that date falls on. The fourth and final expression subtracts the appropriate number of days (depending on which day of the week) to set a new date the previous Monday (or whatever day of the week you want to set). In more detail:

The first Expression requires a Reference Date where we know which day of the week it occurs on, and is not within the range of dates in the source data. (This is because the sales on the same of day as the reference date would create a Divide-by-O error.) This date should also land on the same day of the week (That means that if you want your week to start on Wednesday, the Reference Date should be a Wednesday, just to be clear.)

The example uses the date October 21, 2019, which is a Monday.

DateTimeDiff("2019-10-21",[TransDate],"days")/7

The first part subtracts the Date from the data stream from the Reference Date, which returns an integer. This count of days is divided by 7, so the field is fixed decimal to 6 places after the decimal point. For each day of the week, this divide-by-7 will create a regular pattern after the decimal point (equal to 0/7, 1/7, 2/7, etc):

Monday           XXX.000000 (=0/7)

Tuesday           XXX.857143 (=1/7)

Wednesday     XXX.714286 (=2/7)

Thursday         XXX.571429 (=3/7)

Friday              XXX.428571 (=4/7)

Saturday          XXX.285714 (=5/7)

Sunday            XXX.142857 (=6/7)

And that’s the power of fractions!

The second expression multiplies the number by 1,000,000, which effectively moves all six digits to the left side of the decimal point. It also turns the value into a string, because a string is required for the next step.

The third expression uses the last five digits (which are now the right five characters) to assign a day string to each fraction. Technically, you could skip this step and just use the digits, but I included this step to make it easier to customize when the week needs to start on a day other than a Monday.

IF Right([DateDiff2],5) = "00000" THEN "Monday"

ELSEIF Right([DateDiff2],5) = "57143" THEN "Tuesday"

ELSEIF Right([DateDiff2],5) = "14286" THEN "Wednesday"

ELSEIF Right([DateDiff2],5) = "71429" THEN "Thursday"

ELSEIF Right([DateDiff2],5) = "28571" THEN "Friday"

ELSEIF Right([DateDiff2],5) = "85714" THEN "Saturday"

ELSEIF Right([DateDiff2],5) = "42857" THEN "Sunday"

ELSE "Error" ENDIF

To change this expression, the first line should have the day of the week you want to be the beginning of each week, and then follow each THEN down the lines in order.

The fourth expression subtracts the correct number of days, based on the day of the week, to get back to the previous Monday. Each row will now have an additional date field that contains the previous Monday:

IF [DayName] = "Monday" THEN [TransDate]

ELSEIF [DayName] = "Tuesday" THEN DateTimeAdd([TransDate],-1,"days")

ELSEIF [DayName] = "Wednesday" THEN DateTimeAdd([TransDate],-2,"days")

ELSEIF [DayName] = "Thursday" THEN DateTimeAdd([TransDate],-3,"days")

ELSEIF [DayName] = "Friday" THEN DateTimeAdd([TransDate],-4,"days")

ELSEIF [DayName] = "Saturday" THEN DateTimeAdd([TransDate],-5,"days")

ELSEIF [DayName] = "Sunday" THEN DateTimeAdd([TransDate],-6,"days")

ELSE Null() ENDIF

Remember to change this expression as well if you want to change the day each week begins. Also note that it will create Null cells if the Reference Date – Data Date = 0.

Now that there’s a new date field [WeekOf] that consistently starts at each Monday, it’s easy to use the Summarize tool to Group by and get your weekly sales totals, traffic averages, click-thrus, or whatever else you need to measure by the week. Easy!

[GeoSinam_dup_113]

AFC CWarren (Atom First Class),

As an old boss used to say " ....plagiarrism saves time". Much obliged for the week calc primer and intriuging "fractional day of week" option. Alteryx folk - please award new rank badges accordingly.

Thanks,

Sinam

[jdunkerley79]

Saw this post, thought would show a couple of other formula to produce the same result.

The weekday can be extraced using the DateTimeFormat function:

DateTimeFormat([Date],"%a")

will give the three letter name for a week day.

Using a switch statement we can convert this to days since Monday

Switch(DateTimeFormat([Date],"%a"),NULL(),"Mon",0,"Tue",1,"Wed",2,"Thu",3,"Fri",4,"Sat",5,"Sun",6)

FInally just need to add this to the date:

DateTimeAdd([Date], -Switch(DateTimeFormat([Date],"%a"),NULL(),"Mon",0,"Tue",1,"Wed",2,"Thu",3,"Fri",4,"Sat",5,"Sun",6), "days")

[ephij]

You can also use a similar reference date method and multiply back to get the same day of the week as the reference date...What the script below does is

1. get the number of days between the reference date and my date divided by 7 to give 'weeks'.
   • the decimal of the result represents the partial week we need to subtract from my date to get to the same date as my reference.
2. So we take the difference of the 'weeks' and the floor of the 'weeks' value to isolate the partial week and multiply that by 7 to convert it back to days (this has to be rounded to the nearest integer due to precision errors)
3. Days can then be subtracted from my date to give the desired day of the week.

datetimeadd([mydate],-round((DateTimeDiff([mydate],'1950-01-01','days')/7-floor(DateTimeDiff([mydate],'1950-01-01','days')/7))*7,1),'days')