# Rolling Average Numbers

Hey,

I'm strugguling with 2 things and I haven't been able to find anything related. So I'm trying to sum the past 12 months daily so I can then divided by 365 and save that number. Then I need to show side by side this new number rolling i.e day 1 of the month = 166, then day 2 of the month (166+166=332), then day 3 (332+166=498) and so on, while showing the current month numbers day 1 of the month 120, day 2 of the month (120+166=286), then day 3 of the month (286+200=486). I'm looking for a beast mode that can help me accomplish this. Appreciate any help!

• NY 🟠

Your question is a bit unclear.

For the last 12 months calculation, you can do something like this:

sum(case when `your_date_field` >= dateadd(`your_date_field`, interval -1 year) and `your_date_field` <= `your_date_field` then `total_value` else 0 end)/365

Your second part is not clear. If there are 28 days of the month do you want to show all 28 days and their respective values? This would be very long and a tedious process as each day would have to be its case statement.

it could be something like:
CONCAT('DAY 1 ',DAY(`your_date_field`), ' ' ,sum(case when (MONTH(`your_date_field`) = MONTH(Current_date) and DAY(`your_date_field`) =1   then `total_value` else 0 end), ' DAY 2 ',DAY(`your_date_field`), sum(case when (MONTH(`your_date_field` ) = MONTH(Current_date) and DAY(`your_date_field`) >= 1 and DAY(`your_date_field)` <= 2   then `total_value` else 0 end),--and so on  until 31).

I wasnt able to think of anything better. I am sure there could be a possibility of a more cleaner and faster code.