Help with list of subsets for year and year+month

[ksingh19]

Hello!

Need some help with the query in the following link:

Help with list of subsets for year and month multiplying constant values per row

Hello! Sometime back I had a query where I was finding it difficult to create list of subsets multiplying with constant values per row and folks were able to help with this query. Thanks again! Here is the link for this topic Now I am interested in the same thing, but would like to have results for Year and Month instead of just Year and again finding it difficult. I still need subsets for several different years. For solving this problem here, just would like to use 2 subsets 2021 & 2020 to keep it simple. Within each subset, I should be able to have an added variable that can show the sum of last 3 years for any given month. For example, For Feb 2021, we should be able to see sum of last…

It works perfectly as per the requested need on year & month where we want to see sum of last 3 years for any given month in the year.

I just noticed that if I aggregate data on yearly basis, then the additions are off as it adds all the results from each month and each month aggregates previous years values, thus, adding duplicated figures. Is there a way to aggregate on yearly data that shows the right output for year also. OR the only option would be to have one table for yearly output & one for year & month separately?

Also, I noticed December of each Year would give the right result for the year output. Of course, we can create a separate table, using this info as well as using the same concept as was done earlier. But if there is a way to aggregate yearly info in the same dataset, it would help to a great extent.

Thanks for your help in advance!

[nirgrahamuk]

@ksingh19

I just noticed that if I aggregate data on yearly basis, then the additions are off as it adds all the results from each month and each month aggregates previous years values, thus, adding duplicated figures.

What did you change in the code ?

[ksingh19]

I didn't change anything yet. I was using group_by on Date ("Calculated_For" column in this code) to see the yearly values, then it aggregates all last 12 months where each of the month already takes previous 3 months values.

So, everything is working right if I check the values on any given month and that year as we wanted. But If I want to see only the year values without considering months , then it messes up the calculations. For yearly result, it should be same as that of December month of any year as it will cover last 3 years summation.