How to transform daily time series to monthly data and keeping the years ?

peterdw

These are the dataset statistics.  As you can see it runs over several years (from jan 2015 till may 2020)



Here is a sample of how my data looks like before transformation.



How can I transform my dataset to something like this ?
Sum of Sales per month per year.



I already tried the following:
- convert date to numerical (keep old attribute) => date_month
- aggregate sales to sum(sales)
- removed duplicates for date_month
- inner join on date_month



But this results in a sum of months over ALL the years instead.  I only have 12 months now.


Any suggestions ?

hbajpai

@peterdw
You could avoid two steps by using date parse custom after your date str custom in generate attributes step. 

hbajpai

Hey @peter_dw,

You should generate a attribute which has date_month combined with year, like 01-2015, 02-2015 and then you can aggregate over this attribute which will help you achieve the table of sum of sales over every month specific to years rather just month specific as your results indicate. Let me know if this works for you.

peterdw

Thanks for the suggestion, I eventually solved it like this:

1. I generated a new attribute (month_year) based on my existing date value with this expression:
   date_parse_custom(date_str_custom(date,"1/M/yyyy", "be"),"dd/MM/yy","be")
   This sets the date to the beginning of the month
   
   
   
2. Then I do a 'Date to Nominal' with date format dd/MM/yyyy
   
   
3. Then a 'Nominal to Date' with date format dd/MM/yyyy
   
   
   
4. On this date I can then aggregate correctly

hbajpai

@peterdw
You could avoid two steps by using date parse custom after your date str custom in generate attributes step.