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

BrandonC · 2021-12-07T16:21:46+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

Crisocir

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 )

All comments

MarkSnodgrass

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.

Crisocir

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

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.

BrandonC

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!