Range of dates grouped by count of months

[Swayam_Sarkar]

Hello,

I am working with a data set where the dates are in a range (start_date, end_date). From this date range I am trying to summarize the data for the count (number of occurrences) in each month. For example the data is in this format, with additional columns on the right (which need to be aggregated per month & year).

start_date	end_date
05-02-2015	01-06-2016
08-03-2015	08-03-2015
09-03-2015	13-03-2015

Based on this data, the output I am expecting is:

year	month	occurrences
2015	Feb	1
2015	Mar	3
2015	Apr	1
2015	May	1
2015	Jun	1
2015	Jul	1
2015	Aug	1
2015	Sep	1
2015	Oct	1
2015	Nov	1
2015	Dec	1
2016	Jan	1
2016	Feb	1
2016	Mar	1
2016	Apr	1
2016	May	1
2016	Jun	1

Any help in this regard would be highly appreciated.

Best,
Swayam Sarkar. 

[DavidP · Accepted solution]

You can use generate rows to convert each date range to to year and month rows as below, but then it is not clear how you'll know how to split the quantities for a date range to each month.

[DavidP]

Or can you simply add a Summarize?

[Swayam_Sarkar]

Hello David,

Thank you for your reply. The generate rows tool you suggested could work and provide the output I need. As for splitting the date quantities to month and year, I just need to check if there is a count for that month / year, so I once I have the long table for all generated rows, summarizing the output would give me what I need 🙂