Aggregate Records by Date Range in Reports

Prompt

Aggregate the field SLA Expiration Date on Account by month and week in a report.

Solution

• Add the date SLA Expiration Date to a report.
• On the outline view, click the down carrat next to “Columns.” Then, select “Add Row Level Formula.”
  • Aggregate by Month: MONTH(Account.SLAExpirationDate__c)
  • Aggregate by Week: MOD(FLOOR(Account.SLAExpirationDate__c - DATE(2020,01,06))/7,52)+1
    • The date used should be the first Monday of the year.