Compare a beast mode to an arbitrary date range
I've been asked to create a trend that shows the number of open projects (y-axis) for an arbitrary date range(x-axis). A sample of my data looks like this:
Project_ID | Date_Opened | Date_Closed |
---|---|---|
1 | 1/1/2024 | 4/5/2024 |
2 | 1/18/2024 | 4/6/2024 |
3 | 2/2/2024 | 3/1/2024 |
4 | 2/9/2024 | |
5 | 2/27/2024 | |
6 | 3/14/2024 | 4/20/2024 |
7 | 4/3/2024 | |
8 | 4/11/2024 | |
9 | 4/30/2024 | |
10 | 5/1/2024 | |
The users want to be able to choose a date range, say 4/1/2024 to 4/7/2024, and see how many projects were open for each date interval in the range they selected (trending). The data above would yield a result like this:
| 4/1/2024 | 4/2/2024 | 4/3/2024 | 4/4/2024 | 4/5/2024 | 4/6/2024 | 4/7/2024 |
---|---|---|---|---|---|---|---|
Open Projects | 5 | 5 | 6 | 6 | 5 | 4 | 4 |
I'm struggling to create an x-axis of dates independent of dates in my data that I can use in a beast mode to compare to open & closed dates. Is there a way to do this without building it in the ETL? Currently I use a couple variables for custom date ranges, but I've never had to create a range where the dates in my data are not the relevant axis. I'm trying to avoid adding a count by date in my dataset because there are several other metrics I'll need to group by which will result in a large dataset only used for this card.
Thanks for any advice!
Answers
-
Hopefully someone knows an easy way to do this, but the only way I know how to do this would be through ETL by cross joining a set of dates against the project datasets. The flexible joins in ETL help a little bit - the join would be something like custom_date >= date_opened and custom_date < date_closed.
Going to track this in case there's an easier way, would love to know it if there is!
0 -
That is similar to what I did for the first iteration of this card. The request was weekly intervals over a fixed period of time so I was able to keep the size of the dataset down with a count for each date/metric. This one will be a few orders of magnitude larger if I build it the same way, so hopefully I can avoid that!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 129 Manage
- 126 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive