Tips & Tricks: Count the Number of Date Groups in a Report - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Evan PonterEvan Ponter 

Tips & Tricks: Count the Number of Date Groups in a Report

I've figured out a workaround for those interested in grouping report results by a date field and want to count the number of "groups" between the highest date value and the lowest date value, inclusive. This also includes any groupings in between those date values that aren't showing any records (desirable in some situations, not all. Just something to be aware of). While this isn't truly counting "the number of groups that you see in your report", it does work for a lot of situations where you have sequential groups of dates with data - e.g. records with January, February, and March dates on them and your report time frame is set to only show January, February, and March.

I will point out that Salesforce might be developing something to address this natively (https://www.salesforce.com/blog/2019/02/ideaexchange-formulas-dates.html). They mention that:
[The] team paired the two ideas together when scoping the feature, which helps report users organize and summarize data to answer questions such as:
  • When was the last time we contacted a customer?
  • How long, from the project start date, did it take to close an opportunity?
  • What’s the average number of records created within a given timespan?
The article goes on to say:
The initial beta functionality for the Summer ‘19 release will allow comparisons via date/time functions, and is currently in development for the Lightning Experience. Additional functions will be supported upon the general availability of the feature, planned for the Winter ‘20 release.

Which sounds like we should cross our fingers for a Winter '20 release. Until then, try this out:

You will need to create a formula field to correspond with the time frame for your group (see #1 below). This formula field assigns a number sequence to the record based on the date field. The starting number and actual values are arbitrary - this just needs to be a sequential series of numbers that are consistent for all records on the object so that you can calculate a difference between two records.

1. Create a formula field on the object in question.
Formula Field

Use the "number" data type with no decimal places.
Number Formula Options

Anything surrounded by brackets [ ] (including the brackets) needs to be replaced with your value.

- - - 1a. For fiscal year groupings, name the field "Fiscal Year of [date field name]" and use this formula. I'm using a fiscal-year-ending model here. You may want to use fiscal-year-beginning if your organization does so that there's no confusion (see below the code snippit), but it really doesn't matter since we are interested in the difference between two records. Also, I recommend storing your fiscal year start month and start day in custom settings so you don't hardcode those values here:
YEAR([date_field_API_name])
+
IF(
    MONTH([date_field_API_name]) > [Fiscal Year Start Month]
    ||
    (
        MONTH([date_field_API_name]) = [Fiscal Year Start Month]
        &&
        DAY([date_field_API_name]) >= [Fiscal Year Start Day]
    ),
    1,
/*ELSE*/
    0
)
For Fiscal Year Beginning, swap the plus sign for a minus sign on line 2, then swap the 1 and 0 on lines 10 and 12

- - - 1b. For calendar year groupings, name the field "Year of [date field name]" and use this formula:
YEAR([date_field_API_name])

- - - 1c. For calendar quarter groupings, name the field "Sequenced Quarter of [date field name]" and use this formula (works for fiscal quarters too as long as your fiscal months start on Jan 1, Apr 1, Jul 1, and Oct 1 - e.g. your fiscal year starts on Apr 1 and you have 3-month quarters resulting in fiscal months starting on Apr 1, Jul 1, Oct 1, and Jan 1):
IF(!ISBLANK([date_field_API_name]),
    CEILING(MONTH([date_field_API_name]) / 3)
    +
    4 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)

- - - 1d. For calendar month groupings, name the field "Sequenced Month of [date field name]" and use this formula (works for fiscal months too as long as your fiscal months start on the 1st of each calendar month - e.g. Apr 1, May 1, Jun 1, etc):
IF(!ISBLANK([date_field_API_name]),
    MONTH([date_field_API_name])
    +
    12 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)

- - - 1e. For calendar week groupings, name the field "Sequenced Week of [date field name]" and use this formula. This is for Sunday - Saturday weeks. To change which day of the week you want to start on, see below code snippit. I chose January 1, 1905 as my magic date because it's the beginning of a year pretty far in the past that falls on a Sunday. Even if your date value is before January 1, 1905, the calculations will still work since we are only concerned with the difference between two records:
IF(!ISBLANK([date_field_API_name]),
    FLOOR(([date_field_API_name] - DATE(1905,1,1)) / 7,
/*ELSE*/
    NULL
)
To start the week on Monday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,2)) / 7,

To start the week on Tuesday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,3)) / 7,

To start the week on Wednesday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,4)) / 7,

To start the week on Thursday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,5)) / 7,

To start the week on Friday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,6)) / 7,

To start the week on Saturday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,7)) / 7,

- - - 1f. For daily groupings, name the field "Sequenced Day of [date field name]" and use this formula:
[date_field_API_name] - DATE(1905,1,1)


2. In your report, create a custom summary formula.
To calculate the date groupings between the highest date and lowest date, specify the MAX of your formula field from #1 above, then subtract the MIN of your formula field from #1 above, then add 1 (since we want to count both the first and last group, not just what's in between).
[YourObjectHere].[YourNewSequenceFieldHere]:MAX
-
[YourObjectHere].[YourNewSequenceFieldHere]:MIN
+
1
Here's my example - I want to know how many "week" groups are in my report (Just for demo purposes to prove this method is calculating the right number):
Custom Summary Formula Counting Weeks
(this can be displayed at all levels or only at the grand total level)

Here's what it calculates when you run the report:
Report Showing Number of Weeks

Then here's a formula to calculate Average Number of Records per Week:
RowCount
/
(
    [YourObjectHere].[YourNewSequenceFieldHere]:MAX
    -
    [YourObjectHere].[YourNewSequenceFieldHere]:MIN
    +
    1
)
Display this at the grand total grouping only. You should see an accurate average calculation that takes into account how many date groupings you have in your report. Here's what mine looks like:
Average Records per Week

Hope this helps anyone out there trying to calculate something similar in their reports. Comment below with suggestions for improvement, or to celebrate when Salesforce builds this in natively.

Shout out to SteveMo for suggesting I post this. Also his tweet (https://twitter.com/SteveMoForce/status/1130904510833090560) was my motivation to buckling down on this endeavor
Best Answer chosen by Evan Ponter
Evan PonterEvan Ponter
Couple of improvements since I wrote this. Here are revised formulas for step 1. PLUS a few bonuses...


1a "Fiscal Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.

I'm using a fiscal-year-ending model here. You may want to use fiscal-year-beginning if your organization does so that there's no confusion (see below the code snippit), but it really doesn't matter since we are interested in the difference between two records. Also, I recommend storing your fiscal year start month and start day in custom settings so you don't hardcode those values here:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    +
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        1,
    /*ELSE*/
        0
    ),
/*ELSE*/
    NULL
)
For Fiscal Year Beginning use:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    -
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        0,
    /*ELSE*/
        1
    ),
/*ELSE*/
    NULL
)


1b "Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1c. "Sequenced Quarter of [date field name]"
No changes needed. Same description from above:

For calendar quarter groupings, name the field "Sequenced Quarter of [date field name]" and use this formula (works for fiscal quarters too as long as your fiscal months start on Jan 1, Apr 1, Jul 1, and Oct 1 - e.g. your fiscal year starts on Apr 1 and you have 3-month quarters resulting in fiscal months starting on Apr 1, Jul 1, Oct 1, and Jan 1):
IF(!ISBLANK([date_field_API_name]),
    CEILING(MONTH([date_field_API_name]) / 3)
    +
    4 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1d. "Sequenced Month of [date field name]"
No changes needed. Same description from above:

For calendar month groupings, name the field "Sequenced Month of [date field name]" and use this formula (works for fiscal months too as long as your fiscal months start on the 1st of each calendar month - e.g. Apr 1, May 1, Jun 1, etc):
IF(!ISBLANK([date_field_API_name]),
    MONTH([date_field_API_name])
    +
    12 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1e "Sequenced Week of [date field name]"
This should really be using the MFLOOR function to handle dates before January 1, 1905. Plus my original post was missing a parenthesis on line 2 (oops!!). Use this instead for a Sunday - Saturday week:
IF(!ISBLANK([date_field_API_name]),
    MFLOOR(([date_field_API_name] - DATE(1905,1,1)) / 7),
/*ELSE*/
    NULL
)
Here's line 2 for a Monday - Sunday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,2)) / 7),
Here's line 2 for a Tuesday - Monday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,3)) / 7),
Here's line 2 for a Wednesday - Tuesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,4)) / 7),
Here's line 2 for a Thursday - Wednesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,5)) / 7),
Here's line 2 for a Friday - Thursday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,6)) / 7),
Here's line 2 for a Saturday - Friday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,7)) / 7),


1f "Sequenced Day of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    [date_field_API_name] - DATE(1905,1,1),
/*ELSE*/
    NULL
)


AND NOW INTRODUCING...
1g "Sequenced Business Day of [date field name]"

That's right, claiming the 1g spot is the "Sequenced Business Day of [date field name]" field. This will allow you to count the number of Business Days (Monday - Friday) in your report. You are still creating a number field with no decimal places, and you'd use this formula:
IF(!ISBLANK([date field name]) && WEEKDAY([date field name]) > 1 && WEEKDAY([date field name]) < 7,
    (MFLOOR(([date field name] - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY([date field name]),
/*ELSE*/
    NULL
)
Saturdays and Sundays will evaluate to a null value - which means they will be ignored completely even if they appear in your report. The weekdays will each be numbered in sequence. I checked this one for syntax, dates before 1/1/1905, etc so it should be an easy copy-and-paste for you. Then follow step 2 from the original post to create the custom summary formula in your report.


BONUS TIP 1:
You don't need to group your report results by the timeframe you are averaging. Confused? Here's my example. I am calculating the average number of records per week and I want to know that "weekly average" for a time frame spanning the whole fiscal year. I don't need to group my report results by week, only by fiscal year. The custom summary formula will take care of calculating the correct averge for each fiscal year, and the overall time frame in the total column
Average Per Week for Fiscal Years


BONUS TIP 2:
When using multiple groupings, you can use the custom summary formula as-is to find the difference between the maximum and minimum dates in each grouping, or use the PARENTGROUPVAL function to get the maximum and minimum dates from a higher grouping. This allows you to factor in the entire timeframe regardless of the data present in each grouping.

This example is for a report with row and column groupings (formerly known as a Matrix Report). This will need to be displayed at a grouping level (in my case Hostel/Community for the row and Start Date for the column).
RowCount
/
(
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MAX, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    -
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MIN, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    +
    1
)

There have been 9 weeks in FY 2020. Austin had a record each week, their average per week is 1. Nice.
Eastham had a record in each of the first 3 weeks, but none after that. The calculation for Eastham uses 3 as the numerator (the number of records for that group) and 9 as the demoninator (which is taken from the row grand summary for that column) and results in 0.33. For the completed years, each hostel is getting an average calculated based on a 52-week year regardless of having records in the first and last (or any) week of that year.
Average per Week for Entire Time Frame

All Answers

BhavinBhavin (salesforce.com) 
This is AWESOME, Evan! Thank you for sharing! :)

Best,
Bhavin
Evan PonterEvan Ponter
Couple of improvements since I wrote this. Here are revised formulas for step 1. PLUS a few bonuses...


1a "Fiscal Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.

I'm using a fiscal-year-ending model here. You may want to use fiscal-year-beginning if your organization does so that there's no confusion (see below the code snippit), but it really doesn't matter since we are interested in the difference between two records. Also, I recommend storing your fiscal year start month and start day in custom settings so you don't hardcode those values here:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    +
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        1,
    /*ELSE*/
        0
    ),
/*ELSE*/
    NULL
)
For Fiscal Year Beginning use:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    -
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        0,
    /*ELSE*/
        1
    ),
/*ELSE*/
    NULL
)


1b "Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1c. "Sequenced Quarter of [date field name]"
No changes needed. Same description from above:

For calendar quarter groupings, name the field "Sequenced Quarter of [date field name]" and use this formula (works for fiscal quarters too as long as your fiscal months start on Jan 1, Apr 1, Jul 1, and Oct 1 - e.g. your fiscal year starts on Apr 1 and you have 3-month quarters resulting in fiscal months starting on Apr 1, Jul 1, Oct 1, and Jan 1):
IF(!ISBLANK([date_field_API_name]),
    CEILING(MONTH([date_field_API_name]) / 3)
    +
    4 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1d. "Sequenced Month of [date field name]"
No changes needed. Same description from above:

For calendar month groupings, name the field "Sequenced Month of [date field name]" and use this formula (works for fiscal months too as long as your fiscal months start on the 1st of each calendar month - e.g. Apr 1, May 1, Jun 1, etc):
IF(!ISBLANK([date_field_API_name]),
    MONTH([date_field_API_name])
    +
    12 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1e "Sequenced Week of [date field name]"
This should really be using the MFLOOR function to handle dates before January 1, 1905. Plus my original post was missing a parenthesis on line 2 (oops!!). Use this instead for a Sunday - Saturday week:
IF(!ISBLANK([date_field_API_name]),
    MFLOOR(([date_field_API_name] - DATE(1905,1,1)) / 7),
/*ELSE*/
    NULL
)
Here's line 2 for a Monday - Sunday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,2)) / 7),
Here's line 2 for a Tuesday - Monday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,3)) / 7),
Here's line 2 for a Wednesday - Tuesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,4)) / 7),
Here's line 2 for a Thursday - Wednesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,5)) / 7),
Here's line 2 for a Friday - Thursday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,6)) / 7),
Here's line 2 for a Saturday - Friday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,7)) / 7),


1f "Sequenced Day of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    [date_field_API_name] - DATE(1905,1,1),
/*ELSE*/
    NULL
)


AND NOW INTRODUCING...
1g "Sequenced Business Day of [date field name]"

That's right, claiming the 1g spot is the "Sequenced Business Day of [date field name]" field. This will allow you to count the number of Business Days (Monday - Friday) in your report. You are still creating a number field with no decimal places, and you'd use this formula:
IF(!ISBLANK([date field name]) && WEEKDAY([date field name]) > 1 && WEEKDAY([date field name]) < 7,
    (MFLOOR(([date field name] - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY([date field name]),
/*ELSE*/
    NULL
)
Saturdays and Sundays will evaluate to a null value - which means they will be ignored completely even if they appear in your report. The weekdays will each be numbered in sequence. I checked this one for syntax, dates before 1/1/1905, etc so it should be an easy copy-and-paste for you. Then follow step 2 from the original post to create the custom summary formula in your report.


BONUS TIP 1:
You don't need to group your report results by the timeframe you are averaging. Confused? Here's my example. I am calculating the average number of records per week and I want to know that "weekly average" for a time frame spanning the whole fiscal year. I don't need to group my report results by week, only by fiscal year. The custom summary formula will take care of calculating the correct averge for each fiscal year, and the overall time frame in the total column
Average Per Week for Fiscal Years


BONUS TIP 2:
When using multiple groupings, you can use the custom summary formula as-is to find the difference between the maximum and minimum dates in each grouping, or use the PARENTGROUPVAL function to get the maximum and minimum dates from a higher grouping. This allows you to factor in the entire timeframe regardless of the data present in each grouping.

This example is for a report with row and column groupings (formerly known as a Matrix Report). This will need to be displayed at a grouping level (in my case Hostel/Community for the row and Start Date for the column).
RowCount
/
(
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MAX, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    -
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MIN, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    +
    1
)

There have been 9 weeks in FY 2020. Austin had a record each week, their average per week is 1. Nice.
Eastham had a record in each of the first 3 weeks, but none after that. The calculation for Eastham uses 3 as the numerator (the number of records for that group) and 9 as the demoninator (which is taken from the row grand summary for that column) and results in 0.33. For the completed years, each hostel is getting an average calculated based on a 52-week year regardless of having records in the first and last (or any) week of that year.
Average per Week for Entire Time Frame
This was selected as the best answer
Ana FAna F
Hi Evan, 
This post is gold! I find it extremely insightful. 

There is a project that I am trying to work on that might benefit of this, but I am hoping to get some help from you on it. 
I am trying to calculate the average of calls (Activities) a sales rep had by week, but excluding the days in which he/she was out of office. 
I considered creating a custom Object to save the Absences, but how will I relate it to Activities? On the User level I cannot have a rollup summary, as no related lists are permitted.. 
Do you happen to have a recommendation for this please? 
Thank you!
Evan PonterEvan Ponter
Maybe we can get you close, here's what I'm thinking:

To clarify, you want to calculate the average number of calls per day and then have the report show you that calculation on a weekly basis for each sales rep? For example, if a rep has the following number of activities:
Monday: 1
Tuesday: 2
Wednesday: 3
Thursday: 4
Friday: 5
Then you would see an average of 3 calls per day for this rep for that week.

And then in this situation, you only want to count the number of days the rep actually worked:
Monday: 1
Tuesday: 2
Wednesday: absent
Thursday: 4
Friday: 5
So the average number of calls per day for this week would be (5+4+2+1)/4 = 3

You could try storing the absences in a custom object and be sure to relate them to a user (you could make use of the Owner field if this "Absences" object is not the detail object in a master-detail relationship). Then create the "Sequenced Day of Activity Date" on the Activity object as specified in 1f above, or if you just want Monday - Friday then set up a "Sequenced Business Day of Activity Date" field as-in 1g. You will also need to set up the same logic in a formula field on the Absences object.

Then you would have to use a joined report with one block set up for Activities and the other block set up for Absences. Group by user. You won't be able to do a sub-level grouping for the weeks across blocks in a joined report, so this report is going to have to be filtered down to a single week in each block. Hope that's not a show stopper?

In a cross-block custom summary formula, you can use a formula like this (the API names will likely be different in your org)
Activity_Block:RowCount
/
(
    Activity_Block.[YourNewSequenceFieldHere]:MAX
    -
    Activity_Block.[YourNewSequenceFieldHere]:MIN
    +
    1
    -
    (
        Absence_Block:RowCount
        -
        IF(Absence_Block:[YourNewSequenceFieldHere]:MAX = Activity_Block.[YourNewSequenceFieldHere]:MAX,
            1,
        /*ELSE*/
            0
        )
        -
        IF(Absence_Block:[YourNewSequenceFieldHere]:MIN = Activity_Block.[YourNewSequenceFieldHere]:MIN,
            1,
        /*ELSE*/
            0
        )
    )
)

The end result should assume each rep works the full week, but if they have any absences then that day will be subtracted from the calculation. If I have this worked out right, an absence at the beginning or ending of the time period you are filtering down to shouldn't throw off the calculation. I'm assuming there won't be any activities for a rep on the days they are absent, so a Friday absence would already calculate Monday as the min and Thursday as the max days with activites and we won't need to subtract that Friday absence record. A Wednesday and Friday absence would still see Monday as the min and Thursday as the max days with activites, so we would just subtract 1 day to account for Wednesday.

Hope this makes sense and points you in the right direction! Sorry for the limitation with looking at a single week and using joined reports... I don't have any better ideas at the moment to allow for the flexibility with tracking absences.
Jared RileyJared Riley
I'm trying to use this and it seemed to be working for a bit, but now some of my SRFs for Number of Months are showing a value of 2, when I believve they should all be 1. Any idea what would be causing this?User-added image
Evan PonterEvan Ponter
What field are you grouping the columns by? Is it a date field that has the grouping set to show calendar months? Is it the same field that you are using in the summary formula calculation? Could you post a screenshot showing the report groupings and another screenshot showing the formula you used?
Jared RileyJared Riley
I am grouping by Account Owner and Account Name (I believe is necessary for what I am trying calculate, which is average # of records per account over this given time)

User-added image
User-added image
Evan PonterEvan Ponter
Your filters must be allowing for opportunity records that are created in multiple months. This might be desirable for the calculation you want. To find the average number of opportunities created each month for each account owned by each user, your summary formula should be updated to:
RowCount
/
(
    Opportunity.Sequenced_Month_of_Created_Date__c:MAX
    -
    Opportunity.Sequenced_Month_of_Created_Date__c:MIN
    +
    1
)

This should calculate the correct average for each account as you change the time frame filters in your report.
Evan PonterEvan Ponter
Yes, you can wrap any datetime field in a DATEVALUE() function in order to reference just the date portion. So you can use 
DATEVALUE(CreatedDate)
anywhere you need to reference a date field in the above formulas.
Ashley BettilyonAshley Bettilyon
Hi there, 

When i try to use the business days formula, i keep getting a snytax error for not have "=" and then when i add "='" it tells me another error because i have it. Not sure what the work around is.. 


IF(!ISBLANK([LastModifiedDate]) && WEEKDAY([LastModifiedDate ]) > 1 && WEEKDAY([LastModifiedDate]) < 7,
    (MFLOOR(([LastModifiedDate ] - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY([LastModifiedDate ]),
/*ELSE*/
    NULL
)
Evan PonterEvan Ponter
Hi Ashley,

This should be a formula field on the object using the "number" data type and 0 decimal places. when writing the formula, there should be no brackets surrounding the field API names. (Sorry, my examples could be confusing. I made a note in the paragraph afterward to replace everything including the brackets). Also, you should convert the datetime value to a date value for all the calculations. Try this:
IF(!ISBLANK(LastModifiedDate) && WEEKDAY(DATEVALUE(LastModifiedDate)) > 1 && WEEKDAY(DATEVALUE(LastModifiedDate)) < 7,
    (MFLOOR((DATEVALUE(LastModifiedDate) - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY(DATEVALUE(LastModifiedDate)),
/*ELSE*/
    NULL
)

Let me know if that works.
Ashley BettilyonAshley Bettilyon
Thanks Evan! The formula worked but i dont think it is pulling properly. See below, it is showing 29k in Count Day-Test column (where this forumula is) but my date range is only from Nov. 1 2019.
User-added image

 
Evan PonterEvan Ponter
Ashley,

You'll need to add a custom summary formula to your report to calculate the difference between the highest business day and lowest business day in your report results:
Case.Count_Days_TEST__c:MAX
-
Case.Count_Days_TEST__c:MIN
+
1
This is going to calculate 20 for the report you showed. If that's what you are hoping for, great! If you are hoping to get "2" as your answer, I don't have a trick for that. This solution is actually subtracting the lowest value from the highest value, which works great if your data is sequential with no gaps, or you want to count all the days in between even if they have no records.

What are you hoping to achieve with this report? Maybe I can suggest something else to get you the answer you are looking for.
Ashley BettilyonAshley Bettilyon
In a Case History Report  I am trying to find the average count for a period of time based off of Edit Date (period of time can vary depending on time frame you are selecting - it does not stay the same) 

I created a formula field but I dont know what to divide Case.Unique_Case_Count__c:SUM by since the count of the days can change depending on what is selected in the date range for the report. 

Example:
Edit date 12/1 - unqiue case count 30
Edit date 12/2 - unqiue case count 25
Edit date 12/3 - unqiue case count 27

I want to be able to see the average is 27 but the grand summary shows a sum of 82. 
 
Evan PonterEvan Ponter
OK, if you are hoping to calculate "the average number of unique case records that are edited each business day", then try this custom summary formula in that case history report:
Case.Unique_Case_Count__c:SUM
/
(
    Case.Count_Days_TEST__c:MAX
    -
    Case.Count_Days_TEST__c:MIN
    +
    1
)
Your field API names may differ - you can select the proper fields in the formula editor and insert them. With your example data, the calculation would show 27.3333
Michelle (Ruihong) MaiMichelle (Ruihong) Mai
Hi Evan, do you know why I got "0" in Avg record/day. All other are correct

User-added image
Evan PonterEvan Ponter
Hi Michelle,

Can you show me screenshots of the formula field you created on the Service Request object for storing the sequenced day calculation? Also can you show me a screenshot of the "Avg record/day" summary formula field you defined in this report?
Michelle (Ruihong) MaiMichelle (Ruihong) Mai
Hi Evan, thank you for your reply. I think I've found what's going on. Coz I use the bussiness day formular however there is a case the created day was on weekend (Sun) so the value comes up was not existing so then I adjust the formular, I get the result now. It looks like business was on all 7 days NOT 5 days.
Jeffery WrightJeffery Wright

Is this achievable by using snapshots?

Evan PonterEvan Ponter
Hi Jeffery,

Yes, you could set up a reporting snapshot to create records on the same frequency as the date groupings you are trying to capture - E.g. To calculate average number of records created each day, set up a daily snapshot that runs just after midnight and aggregates the number of records created the previous day. Use a formula field with the date data type on the snapshot object to return the day before the created date (to match the records being captured in the aggregate result). Then create a report on these snapshot records and use a summary formula to return the "number_of_records_created:SUM / RowCount".

This idea could be expanded to only count certain days - use a formula field to indicate business days or some other logic that determines if the record should be counted on these reporting snapshot records, or set up a regular checkbox field to indicate if the record should be included so you can manually set certain days to false to exclude holidays, vacation days, etc.

The same could be done for weeks, months, quarters, years, or any fiscal equivalent. This approach would take some more preplanning, may necessitate creating historical records manually, and needs to consider that the snapshot record results are independent of the live data (which makes them prone to becoming out-of-date), but it is definitely an option in the right situations.
Kenneth MenteleKenneth Mentele
Hi All - First off, this is awesome! But I need a little help... 

I am trying to do Average Calls Daily Calls by Business Day - MTD. I input the business day segement from the comment and then did step two to create the summary formula, but I am running into an issue where the Sum of my "Number of Days" field is super high and I can't quite figure out what I am doing wrong. 

User-added image
Anyone know what I am likely doing wrong here? Thanks in advance! 
Evan PonterEvan Ponter
Hi Kenneth,

Which object are you using to keep track of calls? This object should have the date field on it where you will be assigning a Sequenced Business Day. Based on how you named it, I'm thinking you are using the standard Task object and want to assign a sequenced business day using the Due Date field.

So your custom "Sequenced Business Day of Due Date" field should be on the Activity object (this is where you can create a custom field for the Task object) using this formula:
IF(!ISBLANK(ActivityDate) && WEEKDAY(ActivityDate) > 1 && WEEKDAY(ActivityDate) < 7,
    (MFLOOR((ActivityDate - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY(ActivityDate),
/*ELSE*/
    NULL
)
Then create a report using the "Tasks and Events" report type (or any report type like "Opportunities with Activities" if you need to access fields from another object and every single activity record you want to report on always specifies that object in the Related To field). Be sure to add a filter for "Sequenced Business Day of Due Date" not equal to [a blank value]. This way, any calls that were made on the weekend are excluded.

Do you want to calculate a single number showing the average number of calls made per business day in a month? How many months do you want to see in the report results? From what you showed in the screenshot, I'm thinking you want to show task records grouped by day, filtered down to a single month, then calculate the average in the Total row at the bottom. If that sounds right, your custom summary formula should be:
RowCount
/
(
    Activity.Sequenced_Business_Day_of_Due_Date__c:MAX
    -
    Activity.Sequenced_Business_Day_of_Due_Date__c:MIN
    +
    1
)

You can select to display this summary formula result for the grand total only - otherwise you will see the record count listed for each day's grouping in this column.

Hopefully this helps clear up where things went wrong. Let me know if you have any other questions. If it's still not working right, please post screenshots of your custom formula field, your summary formula settings in the report, and let me know what object, date field, and report type you are using.
Kenneth MenteleKenneth Mentele
Thanks for the quick reponse! 

I was going through your steps and I had not included a "Sequence Business Day of Due Date" != '' filter on my report. 

Solution: If anyone is struggling for after step 2, where you have a huge number that doesn't make any sense - add a report filter for "Sequenced By...." field != "" 

Now it looks great! THANK YOU! 
Shirah BrownShirah Brown
Need help guys! 

Trying to replicate the above formula for a calculation on the Activity object - created a formula field, and I have tried this a million ways and I keep getting various errors: 

IF(!ISBLANK(DATEVALUE([CreatedDate]),
    MONTH(DATEVALUE([CreatedDate])
    +
    12 * YEAR(DATEVALUE([CreatedDate]),
/*ELSE*/
    NULL
)

Error: Syntax error. Missing '='
______________________________________________________
IF(!ISBLANK(DATEVALUE(CreatedDate),
    MONTH(DATEVALUE(CreatedDate)
    +
    12 * YEAR(DATEVALUE(CreatedDate),
/*ELSE*/
    NULL
)

Error: Syntax error. Missing ')'
______________________________________________

IF(!ISBLANK(DATEVALUE(CreatedDate),
    MONTH(DATEVALUE(CreatedDate)
    +
    12 * YEAR(DATEVALUE(CreatedDate),
/*ELSE*/
    NULL
))))
 Error: Incorrect number of parameters for function 'YEAR()'. Expected 1, received 2
__________________________________________________
I cannot get this to work :(

My goal is to count number of activities so that I can create a summary formula to calculate average activites entered by month (grouped by assigned, and row grouped by date (grouped by month). 

User-added image
Evan PonterEvan Ponter
Hi Shirah,

Sorry for the confusion. Here is what the formula would be in your scenario:
IF(!ISBLANK(CreatedDate),
    MONTH(DATEVALUE(CreatedDate))
    +
    12 * YEAR(DATEVALUE(CreatedDate)),
/*ELSE*/
    NULL
)

I've just put the finishing touches on an official blog post for this solution over at ReportForce.blog - check it out:
https://reportforce.blog/2020/06/25/count-the-number-of-date-groups-in-a-report-dynamically/
Shirah BrownShirah Brown
Evan - THANK YOU!!! that did the trick - trying my best to deliver some average reports for my business leads, and really appreciate the reply. I will definitely check out the blog!
Ligea AlexanderLigea Alexander
I am trying to find out if each unique case had a weekly meeting during a particular month. Do either of the formulas here work for that?
Evan PonterEvan Ponter
Ligea - I don't think one of these formulas will help with that use case, but if you can you provide some more details I might have a suggestion to help you. What are you hoping the output would show - could you mock this up with some example data? Are "meetings" recorded as event records under the activities for a case? Do you only need to see a list of cases that had meetings during certain weeks, or do you need to see all cases and then have the report denote which ones satisfy the weekly meeting criteria?