Summation report with averages based on Sum

I am working on a report that will take the Daily Sum and report the average daily sum per month

For example I have a module that is our Billable Records - I have a current report that shows me the billables by day based on the Sum of a specific field. I need a report that will take the Sum of that field and produce a daily average per month.

ie:

November | Daily average total billed sum: 56 hours

December | Daily average total billed sum: 62 hours

January | Daily average total billed sum: 60 hours

What I am getting right now is the average per each record put in instead of the average for the duration in the filters

0 Francesc Del Moral Farrarons over 2 years ago
Hi Anna,

Applying that kind of calculations to a SUM will not be possible via standard reporting at this moment. However, you could build that report with advanced reporting and a custom query

As a proof of concept I built something similar on a demo environment, but for Opportunities with expected closed date between August 1st 2023 and December 31st 2023.

Here my custom query:

Fullscreen
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SELECT DATE_FORMAT(opportunities.date_closed,'%Y-%m') month,round(sum(IFNULL(opportunities.amount,0)/IFNULL(opportunities.base_rate,1)),2) sum_amount, round(round(sum(IFNULL(opportunities.amount,0)/IFNULL(opportunities.base_rate,1)),2)/day(last_day(opportunities.date_closed)),2) sum_avg, day(last_day(opportunities.date_closed)) total_month_days FROM opportunities WHERE (((opportunities.date_closed >= '2023-08-01' AND opportunities.date_closed <= '2023-12-31' ))) AND opportunities.deleted=0 GROUP BY DATE_FORMAT(opportunities.date_closed,'%Y-%m') ORDER BY DATE_FORMAT(opportunities.date_closed,'%Y-%m') ASC

Then I built a Data Format to execute the query and this is what I got:

As you see, the division of "sum_amount" between "total_month_days" gives as result the value in "sum_avg" for every month shown on the report.

I hope this helps getting the solution for your specific module.

Best regards,

Francesc