Options

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

peterdwpeterdw Member Posts: 5 Newbie
in Help
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 ?
Tagged:

Best Answer

Answers

  • Options
    hbajpaihbajpai Member Posts: 102 Unicorn
    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.
    Best,
    Harshit
  • Options
    peterdwpeterdw Member Posts: 5 Newbie
    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

Sign In or Register to comment.