Ask Search:
Evan PonterEvan Ponter 
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
Tate HamiltonTate Hamilton 
I'd like to create a report on all records of a custom object, grouped by "Created By" and "Created Date." I'd like to put it on a dashboard and be able to group it in a bar chart with Created Date along the horizontal axis and then stacked by "Created By."

Here's the tricky part - I only want to highlight 3 people from the Created By group and I want to group all others who aren't those three people into their own lumped section. So there would be 4 groups stacked up - Person 1, Person 2, Person 3, and OTHERS.

Does this make sense? Is anything like this possible?
Best Answer chosen by Tate Hamilton
Srinivasa RayapuriSrinivasa Rayapuri
Hi Tate

You can create bucketing column on Lead Owner and group them accordingly and then use that in your dashboard
User-added image
Andrew WoodAndrew Wood 

I am aware of the Lead Report with Opportunity Information. One limitation I have come across though is that this report will exclude Opportunities which were created directly as an Opportunity and didn't orgininate as a Lead.

Is there a way to run a Lead Report and include Opportunities which didnt come from a Lead?

Best Answer chosen by Andrew Wood
Geoffrey FlynnGeoffrey Flynn
Hi Andrew,
You could do a join report for this
Report Type: Leads with Converted Lead Information filtered by Opportunity ID <> ""
Report Type: Opportunity
Joined by Opportunity Name or ID

You would get all Opportunities but it would also show the lead if applicable

User-added image
Steve MolisSteve Molis 
Best practice tip:
Whenever you’re creating a Formula Field, Workflow Rule, Process Builder (or posting a Question about your Formula in the Answers Community), create a List View that includes all of the Fields that your Formula is evaluating side-by-side along with your Formula Field result (if it’s a Cross-Object Formula create a Report).  

Just because you clicked that Check Syntax button and you got that little green message saying “Congratulations!!!  No syntax errors in merge fields or functions. (Compiled size: 4998 characters)” doesn’t mean you’re home free. It just means you have the right combination of AND’s, OR’s, &&’s, ||’s, etc. you could still have a flaw or loophole in your Formula Logic.  

If you think getting a Syntax Error sucks, just wait’ll you have to explain to your boss why their Pipeline and Forecast Reports and Dashboards are wrong because you didn’t check the results of that shiny new Formula Field you built.
 
[Insert Winston Wolfe’s parable about premature self-congratulation]
https://www.youtube.com/watch?v=mxuHYVmWlmU

Formula with QC List View
User-added image
User-added image


Cross-Object Formula with QC Report
User-added image
User-added image


 
Best Answer chosen by Steve Molis
Adam MarksAdam Marks
Steve, please make sure to mark a best answer! #keepourcommunityclean!


(that felt so ditry to type)
Ian GendreauIan Gendreau 
I see lots of Ideas for expanding the bucket limit being raised from 20 items, so I'm wondering if there is a good workaround.

I wanted to create a bucket called "Metro" with 3 buckets in it: Philly Metro, NY Metro, and Other.  For each bucket, I want to put a list of zip codes that make it Philly or NY Metro area.  But I quickly learned, I can only put 20 zip codes in before it stops me.  I need the ability to have more like 100 or more zip codes.

Is there another way to do this to get each of those sectors grouped in the same report?
Best Answer chosen by Ian Gendreau
david chengdavid cheng
With that many values, I think your only option is to write an Apex trigger for account and/or contact and create a custom Zip Code Lookup object.  The trigger would get the region from the zipcode lookup object and populate the metro area field in your account/contact.
Alban MarchadierAlban Marchadier 
Hello,

I cannot add more that 20 values in the bucket. And yet I need to add more countries for my "BU Europe". 
How can I mitigate this issue? Is there a workaround you can advise or am I missing something?

Thanks so much in advance for your help!

Alban

User-added image
Best Answer chosen by Alban Marchadier
Darren KnappDarren Knapp 
I see this article:  https://success.salesforce.com/answers?id=90630000000gtjkAAA   but power of 1 is not working for me on this one. 
In this screenshot below, I'd like to get the average of emails per month, so I need the RowCount / number of groupings (in this case, 3).  I have done that manually, but would love to change this to a dynamic formula.  
User-added image
Is this possible?
Best Answer chosen by Darren Knapp
Steve MolisSteve Molis
Unfortunately a Date is not an object/record unto itself, so it can't be counted and used within Report Summary Formula Math.  
If the number of Months in your report is a known quantity, like the Report Filter is set to LAST 3 MONTHS, or something like that you can "hard code" the number of Month Groupings into the Formula like this
( RowCount / 3 )
But if the the number of date Groups is fluid, then you're f*cked 
 
Georgia SerpeGeorgia Serpe 
I have  a formula field that calculates the duratoin of a task:  
TEXT(
 FLOOR(NOW()- CreatedDate))

However, I am trying to convert this to a number because in my report, I can't summarize as either average or total.  

What do I need to do to change this to a number field? 

Thanks.
Best Answer chosen by Georgia Serpe
Jesse WolffJesse Wolff
Sorry... I meant you should be able to edit the field and change the Formula Return Type.
User-added image
 
Brad DunnBrad Dunn 
Hi all,

I am trying to add percentages to the below report. What I'd like to show is each status divided by the total of the statuses for each practice line. For example, in the regulatory practice there is 1 unit in the preparing phase. There are a total of 9 units in the regulatory practice. What formula do I use to have "regulatory preparing / regulatory total" or 1/9 to equal 11%?

A big thanks in advance to anyone who can assist!

Best,

Brad

EOI Table

 
Best Answer chosen by Brad Dunn
Steve MolisSteve Molis
Like this?
RowCount / 
PARENTGROUPVAL(RowCount, Opportunity.Do_you_owe_SteveMo_a_beer__c, COLUMN_GRAND_SUMMARY)

User-added image
Gustavo SelujaGustavo Seluja 
The display issue with truncation of long text fields has been reported several times already.  I want to double check this is yet to be fixed, correct? It appears to only affect Lightning Experience, correct?  When I compare both, Classic seems to display all characters.  As it's been suggested already, the exported report, xls or xlsx formats, displays the field information correctly.  Thank you.
Best Answer chosen by Gustavo Seluja
Gustavo SelujaGustavo Seluja
@Jason Morales

Hi Jason.  After posting it I remembered it was already answered by Salesforce back in February! (one forgets! 😇).

https://success.salesforce.com/0D53A00004Ar820

Anusha Surepeddi (Salesforce)
@Gustavo Seluja (Medio Mundo LLC) - We are working on bringing text wrapping similar to excel or google sheets, however this requires a change in how we render the data on the report run page. So the change is coming for Summer'19 release (safe harbor) since we want to make sure it will perform well.

Please note that the enhanced report view table is not a HTML table like the legacy report view or classic report view. The table in enhanced report view is an advanced lightning design system component which allows for table column actions such as "group rows by this field" similar to the lightning report builder.
Unlike   ·  1 person   ·   Edited February 18, 2019 at 11:25 PM