How do we track if a date occurred within another date column?
Hi,
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!
Best Answer
-
I think you could just sum the results of a case statement
SUM(CASE WHEN DATE_FORMAT(DATE_1, '%b%y') = DATE_FORMAT(DATE_2, '%b%y') THEN 1 ELSE 0 END )
3
Answers
-
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.
**Make sure toany 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
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有