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 

  • 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:

    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