Details about this thread
  • State Not Answered
  • Replies 1 reply
  • Subscribers 315 subscribers
  • Views 412 views
  • Users 0 members are here
Related content from around SugarClub

Summation report with averages based on Sum

Anna Harris

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 Sugar Employee Badge

    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