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.
Solved! Go to Solution.
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 🙂