Calculating the SUNDAY start date of the week from Google Analytics' "WeekOfYear" Dimension

dean_pangelinan

One task that I was faced with recently was trying to determine what the date of the SUNDAY start of a calendar week would be, given only the YEAR and the WEEK number.

 

All of the YEAR(s) and the WEEK(s) would be obtained from Google Analytics as "YYYYmm".

With the help of Joe Ehat of DOMO Technical Support, I came up with the following:

Since the YEAR(s) would be 2017 and later, and the WEEK(s) would be numbered from 1-52...

 

ADDDATE('2016-12-25',
interval ( ( MOD(`Week of Year`,100) + ( ( (`Week of Year` - MOD(`Week of Year`,100) ) / 100 ) - 2017) * 52 ) *7) day)

This starts with the fixed SUNDAY of the LAST week of 2016...

...calculates the WEEK component of the `Week of Year` -- as MOD(`Week of Year`,100)

...then calculates the number of YEAR(s) PAST 2017, and multiplies that number by 52 (weeks) -- as ( ( (`Week of Year` - MOD(`Week of Year`,100) ) / 100 ) - 2017) * 52

...adds the two WEEK counts together and multiplies that result by SEVEN (to aggregate the number of DAY(s) ...and then ADDs those days to '2017-12-25' to calculate the DATE of the SUNDAY of the given 'Week of Year'

 

I then used this "Date" format Beast Mode as my X-AXIS for a Vertical Bar chart so that the SUNDAY of the Week would be shown as the Date on the Category Scale.

Enjoy!

n8isjack

Impressive!