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.

Godzilla

no you can't shift it with the out of the box functionality. You would have to load it in as part of your dataset or build a beast mode but you will lose the time grain functionality if you build a customer date field. 

McSQL

Are you starting with raw dates and the "week-1 2015" is generated by the domo card or does the raw data have a column with "week-1 2015" in it?

mbrady

I am starting with raw dates that domo is transforming into "week-1 2015"

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.

CantStopTheHopp

Is it possible to shift this around to start on a different day of the week other than Sunday? In other words, look at Friday-Thursday instead of Sunday-Saturday.

kshah008

Hi all,

 

Does anybody have any input for @CantStopTheHopp with their follow-up question? If you can't find an answer here, feel free to open a new topic with your question!

Godzilla

no you can't shift it with the out of the box functionality. You would have to load it in as part of your dataset or build a beast mode but you will lose the time grain functionality if you build a customer date field. 

kshah008

@CantStopTheHopp, did Godzilla's response help address your follow-up question?

CantStopTheHopp

Yes that is what I was thinking but was wanting to confirm. Fortunately it wasn't a crucial need so I'm not going to bother looking into it further.

happyzoom

good stuff! Thank you!

Ms_Qube

This solution doesn't work when comparing Y-o-Y data