how to aggregate by the last day of the month available in the dataset
I have a dataset with3 columns:
¦ date ¦ dataset type ¦ number of datasets ¦
date: date format dd/mm/yyyy
dataset type: it's basically text
number of datasets: integer
Here's an example of dataset:
It basically counts everyday the number of dataset per dataset format (pdf, zip, etc). There are missing data for certains days, I mean, in some cases there are days missing like for instance the last day having data in August 2016 was the 29th of August.
I need to sum up the total number of datasets for only the last day appearing each month. I can't use "MonthEnd" beacause I'm not certain that this date exists for every month of my dataset. So basically I need:
1. detect the last day appearing every month of the year
2. sum up all the "number of datasets" for this date.
3. This aggregation becomes the number total of datasets for the current month
July 2016 --> 20 000
August 2016 --> 21 000
guys, I haven found a suitable solution so far and I'm stuck. So, I'll really really appreciate your advice.