How do we track if a date occurred within another date column?

BrandonC
BrandonC Member
in Charting

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

  • Crisocir
    Crisocir Member
    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 )

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Crisocir
    Crisocir Member
    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 )

  • GrantSmith
    GrantSmith Coach

    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!**
  • BrandonC
    BrandonC Member

    Thank you all so much for your time and help here! I ended up using @Crisocir 's solution and it appears to be calculating exactly what was needed!

Categories