How do we track if a date occurred within another date column?
We have two date columns within our dataset and we want to see how many of one date column occurs within the other date column.
Date Column A: Desired date to graph by
Date Column B: Desired qualifier within Date Column A
For example, how many instances of Date Column B within the month of September 2021 occurred within September 2021 of Date Column A?
Additional note that the two date columns are in the same date format.
Any guidance here would be greatly appreciated!
I would create a beast mode to do this comparison:
CASE WHEN MONTH(columnA) = MONTH(columnB) and YEAR(columnA) = YEAR(columnB) THEN 1 ELSE 0 END
Then drag this field into your card and choose Sum for aggregation along with columnA and you should get your totals of when they are the same.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
An alternative beast mode is to use LAST_DAY (returns the date of the last day in the month) instead of MONTH and YEAR:
CASE WHEN LAST_DAY(`Column A`) = LAST_DAY(`Column B`) THEN 1 ELSE 0 END
If you're looking to count all of the occurrences and not just when the values are the same for each record you'd need to use an ETL and group your dataset by column B and get a count of records then join that to your original dataset based on the grouped by date and column A to get the number of occurrences.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
- 7.7K All Categories
- 3 Connect
- 919 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 38 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 19 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 27 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部