Rolling Average Numbers

user0010355

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!

rahul93

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.

user0010355

Thank you for replying back, I know the ask was a bit complicated. We were able to pull it off, but required the use of the Lag function on the ETL and a tedious process of grouping the monthly numbers. Thank you for your input, appreciate it.

rahul93

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.

user0010355

Thank you for replying back, I know the ask was a bit complicated. We were able to pull it off, but required the use of the Lag function on the ETL and a tedious process of grouping the monthly numbers. Thank you for your input, appreciate it.

rahul93

No Problem!! If you can use SQL it is much easier taking that route. Glad you could figure it out.