Converting Week numbers into date ranges

mbrady

 

I've got some quarterly data that I'd like to format a bit. I'd like to change the "week-1 2015" labels (see attached image) to something like "Jan 1-7 2015" etc. I can't seem to find which beast mode formula I could use to accomplish this. Thanks in advance!

 

 

jmo

There are a few options that I have used in the past. I'll outline them below. The main strategy is to shift the date in each row of your data backwards (or forwards) and then extract the portion you want.

 

SUBDATE(`Date`,WEEKDAY(`Date`)-1)

This is the simplest version and returns the full date of the date's Sunday. This returns "2015-01-04" from your example (in 2015, the first and seventh of January fell in different weeks).

 

Note that even though this looks like a date, Domo will consider it a character value. The upshot is that you cannot use the date grain selector on this value, all cards using this Beast Mode will always be locked to weekly.

 

DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%m/%d/%Y')

Same as previous only we format the date so it looks like this: "01/04/2015".

 

CONCAT('Week beg: ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b-%d'))

This version puts the text "Week beg:" and then only shows the month and day of that week's Sunday. Example: "Week beg: Jan 04".

 

CONCAT(DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b %d'),' - ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-7),'%b %d %Y'))

This version gets closest to your example below. Note that the values will span both months and years. This version returns "Jan 01 - Jan 07 2015" for your example.

 

The year is always associated with the end of the week. For the last week of 2014, you would see "Dec 28 - Jan 03 2015".

 

You could get really fancy and do some conditional logic around whether the month at the beginning of the week matches the month at the end of the week (likewise for the year). For each case you would need something similar to the last example. That gets a little complex for my taste but it could certainly be done.