how to break a week between months

WorldWarHulk

Hi DOJO -

Happy Weekend!

Basically, we have a dataset with date columns based on the start of the week and end of the week only.

on the example below, I just concatenated the start and end dates so it shows Aug 28 to Sep 03. if its not concatenated, I can put start date and it would show the start of the week which is Aug-28, or if I put end date, it would show Sep-03

The data in that week is for September, since data is for Thursday and Friday for September.

Is it possible to show it as Sep-01 - Sep-03 instead of Aug-28 - Sep-03?

Thanks!

GrantSmith

You can utilize some date manipulation and some case statements:

CONCAT(CASE WHEN DAYOFMONTH(`End Date`) < DAYOFMONTH(`Start Date`) THEN `End Date` - INTERVAL (DAYOFMONTH(`End Date`) -1) DAY ELSE `Start Date` END, ' - ', `End Date`

Breaking this down:

CONCAT - Joining the two dates together with a ' - ' between

CASE WHEN DAYOFMONTH(`End Date`) < DAYOFMONTH(`Start Date`)

Is the end day of the month less than the start date's day of the month. In other words. Did we cross month boundaries?

`End Date` - INTERVAL (DAYOFMONTH(`End Date`) -1)

Take the end date and subtract the number of days for the given day of the month. -1 here to make sure we don't go to the end of the prior month but to the first of the month (we want day 1 not day 0).

WorldWarHulk

Thanks Grant!

although, I am wondering why I am getting a 10/1/2022 - 10/1/2022 and not getting the 9/25/2022 - 9/30/2022 instead?

nikky

@WorldWarHulk Here is an alt approach - I used Magic2 for this example:

Note that my Date column is labelled Date && my week starts on Sunday ends on Saturday.

Also, it assumes that crossing months defaults to the first day of the later month - thus 10/1-10/1 and NOT 9/25-9/30 ( the week would be defined as 9/25->10/1 you can handle this in the case statement by checking whether the number of days is greater in the prior month vs later month for Cross Month conditions and select which label you want to apply).

Using A formula Tile:

FirstDayOfWeek: date_add(`Date`, interval -WEEKDAY(`Date`)-1 day)

LastDayOfWeek:  date_add(date_add(`Date`, interval -WEEKDAY(`Date`)-1 day), interval 6 day)

Cross Month Boundaries: case when MONTH(`FirstDayOfWeek`) = MONTH(`LastDayOfWeek`) then 0 else 1 end

Label:

case

   when `Cross Month Boundaries` = 0

   then CONCAT(`FirstDayOfWeek`, '-', `LastDayOfWeek`)

   else CONCAT(DATE_SUB(`LastDayOfWeek`, INTERVAL DAYOFMONTH(`LastDayOfWeek`)-1 DAY), '-', `LastDayOfWeek`) 

end

Hope this helps!

WorldWarHulk

Hello Nicky -

Thank you for this!

I have the same problem. As you mentioned, it defaults to 10/1-10/1.

sorry for the newbie question, but how can I show the 9/25 - 9/30 again?

also Aug 28 to Aug 31 is not being shown as well.

basically, I need to show not just the start of the month but also the end of the month,

8/28 - 8/31 and 9/1 - 9/3

other examples are

5/29 - 5/31 and 6/1 - 6/4

6/26 - 6/30 and 7/1 - 7/2

I am sorry if I made some confusion.