Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

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)

Image: https://us.v-cdn.net/6030995/uploads/editor/y5/01n4ipeofc8l.png

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

Image: https://us.v-cdn.net/6030995/uploads/editor/fj/mms0e832eb6e.png

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

Image: https://us.v-cdn.net/6030995/uploads/editor/sc/6zuqmy7b00cs.png

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

Image: https://us.v-cdn.net/6030995/uploads/editor/h6/7x5cagcr2lka.png

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

Image: https://us.v-cdn.net/6030995/uploads/editor/nk/f79qy8s5bvd5.png

Any suggestions ?

Find more posts tagged with

AI Studio

Aggregate

Time Series

Dates + Times

Accepted answers

hbajpai

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

All comments

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:

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
Then I do a 'Date to Nominal' with date format dd/MM/yyyy
Then a 'Nominal to Date' with date format dd/MM/yyyy
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.