how to break a week between months
Hi DOJO -
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?
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).**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
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?0
@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
when `Cross Month Boundaries` = 0
then CONCAT(`FirstDayOfWeek`, '-', `LastDayOfWeek`)
else CONCAT(DATE_SUB(`LastDayOfWeek`, INTERVAL DAYOFMONTH(`LastDayOfWeek`)-1 DAY), '-', `LastDayOfWeek`)
Hope this helps!1
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.0
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 88 Cards, Dashboards, Stories
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive