Calculating the SUNDAY start date of the week from Google Analytics' "WeekOfYear" Dimension
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!
Comments
-
Impressive!
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 465 Domo Developer
- 50 Domo Everywhere
- 106 Apps
- 717 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 27 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 654 ひらめき共有