Ask Search:
Robin RootRobin Root 
I'd like to be able to create a report that will show me the average order (closed opps) size for month, quarter, year.  I know I can export and create a formula field to give me an average, but has anyone done this with a custom field in SFDC so the management team can see this easily?
Best Answer chosen by Moderator (salesforce.com) 
Aaron DeRanAaron DeRan

All in one report? No.
 

But you can create an Opportunities report, make the format "Summary" and then drag over the "Add Formula" option from the left menu which will display a formula window. You can use a formula such as AMOUNT:AVG which would give you the "average size" of your opportunities during a certain time frame. Then you can either duplicate this report with different date ranges or you can save it as a specific date range and just edit the date range and re-run it for each of the time frames you are concerned with.

Laura MondayLaura Monday 
I am an admin and can export reports to excel, but for other admins it says Insufficient Privileges. They're able to run the report and click "Export Details" and choose if they want excel or CSV. But then it says Insufficient Privileges. I have the same role and profile as them, and this happened overnight. They were able to export yesterday.
Best Answer chosen by Laura Monday
Anil Choudary KapuAnil Choudary Kapu
https://status.salesforce.com/generalmessages/239

Ongoing Issue reported by salesforce
Jody MartinJody Martin 
Is there a report I can run to show which reports are part of a dashboard, and if so, which dashboard it's on and who owns it?   I'm trying to clean up some report folders and keep hitting road blocks.  Thanks!
Best Answer chosen by Jody Martin
Sunil SarillaSunil Sarilla
Hi Jody,
Yes it is possible and you will need to create a custom report type with Reports as the Primary Object and Dashboard components as the secondary object and define the relationship between objects as 

see the screenshot below
User-added image
Now run a report using the above report type, this report will give you the list of reports thats used as source in the dashboard but this report will not give you the Dashboard owner.
You will need to create a 2nd report type using Dashboards as the primary object
Then need to the data manipulation outside of salesforce in excel by exporting both the reports
 
Sanjeev ChickerurSanjeev Chickerur 
I am getting a "Function doesn't have a type check definition: daysbetween" while using Daysbetween function in SAQL Wave analytics . Below is the example of the function used.

daysbetween(todate('Created_Date',"dd-MM-yyyy"),todate('Close_Date',"dd-MM-yyyy")).
Unable to figure what is wrong. Please help.
Best Answer chosen by Sanjeev Chickerur
Amit SinghAmit Singh
Let's us do it from scratch

try hardcoing values and then check is this works
daysBetween(toDate("31-12-2015", "dd-MM-yyyy"), toDate("1-1-2016", "dd-MM-yyyy"))

 
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
Roy BattyRoy Batty 
i'm working on an opp report with products and I can't drag/drop most fields into the report.  when i try to drop opp name for example, I just get the red icon with the line through it telling me I can't add it.  seems like it will only let me drag/drop fields that are checkboxes (true/false) showing a # in the sidebar. 

anyone know why this is happening?  are certain reports limited to certain fields?  if so, why do they show in the sidebar?

thanks!
Best Answer chosen by Moderator (salesforce.com) 
Roy BattyRoy Batty
checked "show details" in the preview window & now I can drag/drop them.  fixed!
Erin BlohmErin Blohm 
We currently have a text field that contains billing state. Based on the billing state, we want another field "Region Assignment" to populate with the respective region. 

Here's the formula I currently have. While it's not giving me any syntax errors, the "Region Assignment" field isn't populating anything when I save the formula.

IF( BillingState="AR:IL:IA:KS:MI:MO:NE:ND:OK:SD:TN:TX:WI", "Central", 
IF( BillingState="CT:DE:IN:KY:ME:MD:MA:NH:NJ:NY:OH:PA:RI:SC:VT:WV", "MWSE", 
IF( BillingState="AL:AK:AZ:CA:CO:FL:GA:HI:ID:MS:MT:NV:NM:NC:OR:UT:WA:WY", "C2C", 
null 
)))
Best Answer chosen by Erin Blohm
Deepak AnandDeepak Anand
Try this = 
IF(CONTAINS("AR:IL:IA:KS:MI:MO:NE:ND:OK:SD:TN:TX:WI", BillingState), "Central",
IF(CONTAINS("CT:DE:IN:KY:ME:MD:MA:NH:NJ:NY:OH:PA:RI:SC:VT:WV", BillingState), "MWSE",
IF(CONTAINS("AL:AK:AZ:CA:CO:FL:GA:HI:ID:MS:MT:NV:NM:NC:OR:UT:WA:WY", BillingState), "C2C",
NULL)))
Jackie OrbeJackie Orbe 
Hello! I am trying to add a custom column to show percentages of the reason lost compared to all closed opportunities. Currently it is showing me the record account but I would like to show the percentages for each  category. I would like to basicall devived the "Why was the oppotunuity lost" by the closed record count. Does anyone know the custom formula for this ?User-added image
Best Answer chosen by Jackie Orbe
Steve MolisSteve Molis
I thought you wanted the Percent of each reason lost?  
You had 4 deals, you won 2 and you lost 2
You lost 1 to a Competitor and 1 because they cancelled

The Lost reason doesn't apply to the 2 deals you won. 

Anyway, if that's what you REALLY want, then just use this
RowCount /
PARENTGROUPVAL(RowCount, FULL_NAME)

 
Steve MolisSteve Molis 
User-added image

Opportunity History Reports are f*cking awesome, they allow you to see the complete lifecycle of the Opportunity tracking changes to the "core" Opportunity Metrics: Amount, Stage, Close Date, Probability%, Forecast Category. And unlike Field History Reports they're available right out of the box and immediately have access to historical data from "Day 1" of your SFDC org.  In other words: they work retroactively.  They also have more robust Field Filtering, and access to Cross-Filters (they also don't look like they fell out of the top of the Ugly Tree and hit every branch on the way down).
 
However Opportunity History Reports do have an Achilles’ Heel:  You can’t easily see or filter by the current Stage, Amount, Probability%, Close Date, Forecast Category.  But you can do this (and whole lot more!) just by adding a few simple Formula Fields to the Opportunity Object.  Now some folks are probably saying: 

“I don’t want to add more formula fields, I don’t want my Opportunity to look like a Race Car at the Daytona 500!”. 

Here’s the thing; you don’t need to add the new fields to the Opportunity Page Layout, you just need to give the Users/Profiles “Read” access to the Data in the fields.  So you can keep your precious Opportunity Page layouts as pure as the driven snow in an Ansel Adams landscape.
 
Anyway, it's just an idea that I came up with.  I’m like Leonardo DaVinci, my mind is full of ideas...  although most of them are about as useless as a Wooden Helicopter powered by ropes and pulleys. 
 
And now without further ado here are the fields:
 
Current Stage
Datatype = Formula
Result = Text
Formula = TEXT(StageName)
 
Current Close Date
Datatype = Formula
Result = Date
Formula = CloseDate
 
Current Probability%
Datatype = Formula
Result = Percent 0 decimals
Formula = Probability
 
Current Amount
Datatype = Formula
Result = Currency
Formula = Amount
 
Current Forecast Category
Datatype = Formula
Result = Text
Formula = TEXT(ForecastCategoryName)  

User-added image
Best Answer chosen by Miglena (Salesforce.com) 
Steve MolisSteve Molis
Hi Nate,
If you need a report that shows the current Stage, Close Date, $Amount, Probability% and the historical changes to those fields in 1 Report.    

For eaxmple:  Yuo need a Report of all Opportunities that are currently in the Negotiation/Review Stage with the complete History of Stages, $Amount, Probability, Close Date, etc.  

With a standard Opportunity History Report if you Filter the Report by "To Stage" [equals] Negotiation/Review you'll get any Opportunity that was ever in the Negotiation/Review stage regardless of what Stage they are currently in. 
John StanczakJohn Stanczak 
I'm trying to create a Dashboard that shows an Opportunities Stage Duration in its current stage grouped by stage. I used an Opportunity History Report for the base report and created a custom formula field that pulls the stage value from the Opportunity object to group the opportunities by. However the same Opportunity appears multiple times in the same grouping and lists multiple Stage Durations. Sometiems the Durations are the same and other times they are wildly different. How do I filter the report to show only the correct Duration and show each report only once per grouping?
Best Answer chosen by John Stanczak
Steve MolisSteve Molis
Hi John, 
With an Opportunity History Report you'll see a history record for each change of the Stage, CloseDate, $Amount, Probability% or Forecast Category.  

To get the number of Days that the Opportunity has been in the Current Stage you would need to create a custom Date field that is updated via either a Workflow Rule, Process Builder or Trigger whenever the Stage field is changed.  Then create a custom Formula(Number) field using a Formula like this
 
TODAY() - LastStageChangeDate__c