aggregating average value of a month

chetan_cricut

date	any_hva
1/4/2023	123
1/4/2023	233
1/4/2023	234
2/4/2023	115
2/4/2023	111

I have a table in a following format, same dates are present in multiple records since there are other categories(not shown) in whichany_hva is split.

I want to display a monthly chart in which I want to display average value per day. Just changing the aggregation to avg will not give me the desired output since there are multiple records for a day.

Hence, I wrote a beast mode for the same.


case
when MONTH(event_date_id) in (1,3,5,7,8,10,12)
then sum(any_hva)/31
when MONTH(event_date_id) in (4,6,9,11)
then sum(any_hva)/30
when MONTH(event_date_id) in (2)
then sum(any_hva)/28
end

However, this gives me 30 bars for each month. I need just one per month. What is the ideal way to get an average in this case keeping in mind the number of days of that month.

GrantSmith

Do you have any sorting going on in your chart?

Also to simplify your beast mode you can use the LAST_DAY function to get the number of days in the month instead of having to use a case statement like you are:

SUM(`any_hva`) / MAX(DAYOFMONTH(LAST_DAY(`event_date_id`)))

GrantSmith

Do you have any sorting going on in your chart?

Also to simplify your beast mode you can use the LAST_DAY function to get the number of days in the month instead of having to use a case statement like you are:

SUM(`any_hva`) / MAX(DAYOFMONTH(LAST_DAY(`event_date_id`)))