how to break a week between months
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!
Comments
-
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 ' - ' betweenCASE 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 -
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?
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
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!
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
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive