Converting Week numbers into date ranges

Reply
Highlighted
White Belt

Converting Week numbers into date ranges

 

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!

 

 

DomoTimeFormat.png


Accepted Solutions
Innovation Council jmo
Innovation Council

Re: Converting Week numbers into date ranges

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.

-----
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"
Tags (1)
Major Brown Belt

Re: Converting Week numbers into date ranges

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. 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

All Replies
Green Belt

Re: Converting Week numbers into date ranges

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?

**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
White Belt

Re: Converting Week numbers into date ranges

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

Innovation Council jmo
Innovation Council

Re: Converting Week numbers into date ranges

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.

-----
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"
Tags (1)
Green Belt

Re: Converting Week numbers into date ranges

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.

Moderator

Re: Converting Week numbers into date ranges

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!

Major Brown Belt

Re: Converting Week numbers into date ranges

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. 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Moderator

Re: Converting Week numbers into date ranges

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

Green Belt

Re: Converting Week numbers into date ranges

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.

Visitor

Re: Converting Week numbers into date ranges

good stuff! Thank you!

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!