Ask Search:
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
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
Sylvie SmithSylvie Smith 
Hi there,

Within a consulting environment: I've mandated Start Date on Opportunities though I would like it to be dependent on Stage, i.e. 3-D - Solution / Project Start or later requires Start Date (for resourcing purposes) / any earlier and a start date is indefinite.

(IF at 3-D - Solution / Project Start, Start Date required)

Thanks so much.
Best Answer chosen by Sylvie Smith
Steve MolisSteve Molis
Sure, you can use a Validation Rule like this
AND(
CASE( StageName , 
"Prospecting",1, 
"Qualification",2, 
"Needs Analysis",3, 
"Value Proposition",4, 
"3-D - Solution / Project Start",5, 
"Perception Analysis",6, 
"Proposal/Price Quote",7, 
"Negotiation/Review",8, 
"Closed Won",9, 
0) >= 5,
ISBLANK( Start_Date__c )
)


 
Monika AmmannMonika Ammann 
Hi, I've just completed the above module, but I'm receiving an error when trying to verify my work. It tells me "We couldn't find the expected fields to display in the opportunity list view named 'My Opportunities'. Please double check the instructions." However my Fields to Display are the exact same ones as the ones in the screen shot. And I also made sure I am in the list view of My Opportunities. Has anyone else had this issue?
Best Answer chosen by Monika Ammann
Vera StickerVera Sticker
I had the same problem and this workaround I found in another thread worked for me: go back to table display, sort Expected Revenue by high to low, return to kanban display, and try to verify. The other suggestion in that thread was to make sure that when you drag the opportunity from "needs analysis" to "value proposition," you make sure it's on top of the other two. Hopefully those help you!
Lavanya SanathkumarLavanya Sanathkumar 
Facing issue in my batch test class. I am inserting simple Account and Opportunity in my test class. 
If we set(seealldata = false) the class fails in the sandbox and getting below error 
System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, EventbriteSync.EventbriteOpportunityTrigger: execution of AfterInsert

caused by: System.NullPointerException: Attempt to de-reference a null object

Trigger.EventbriteSync.EventbriteOpportunityTrigger: line 4, column 1: []

Facing the issue in 'Eventbrite OpportunityTrigger' which is from the managed package and we can't see the code. 

If I set (seealldata = true) in test passes in the sandbox but we will get below when moving to Production
System.UnexpectedException: No more than one executeBatch can be called from within a test method. Please make sure the iterable returned from your start method matches the batch size, resulting in one executeBatch invocation. 
Stack Trace: External entry point

 
There any way to fix this?
Best Answer chosen by Lavanya Sanathkumar
Parani T KParani T K
EventbriteSync.EventbriteOpportunityTrigger issue can be fixed with seeAllData true for now. Don't find any proper solution for that.

But when you make a test class seeAllData true you need to consider it will take all record in the org including your test data, if it's production takes all the production data to process in batch. So you can filter only the test data in your test run.
Nicole ZyvoloskiNicole Zyvoloski 
I'm testing out two ways of creating an Opportunity hierarchy.
1) Simple Lookup field on Opp to another Opp. Pro = ability to nest as many levels as needed. Con = difficulty rolling/summarizing data up the chain.
2) Parent Opportunity object (per this help article: https://help.salesforce.com/articleView?id=000004368&language=en_US&type=1 ). Pro = use DLRS to roll up desired data from child opportunity records. Con = appear limited to single-level relationship.

Use case: about 20% of our revenue comes from multiyear cooperative agreements with different government units. We often have multiple such agreements with one agency/account so account pipelines don't quite meet the need. We want a parent opportunity to house documents related to the authority to work together, store the total amount of the agreement, and rollup opportunity amounts as child opportunities are created (budgeted amounts) and then closed (booked amounts/receivables). For that, I'm finding success with option 2, Parent Opportunity object. However, we'd like to add another layer or two. Say we have a 3-year agreement worth $1.5M that specifies 0.5M must be spent each of the three years. We want to set up that 3-year as the Ultimate Parent. Then we want to create a $0.5M child for each year of the agreement. Then we want to nest the child opportunities (ranging from $10k-$250k) for each year under that. In some cases, there is a need for a fourth level as well. The challenge I'm running into is that I can either have one Parent Opportunity with many child Opportunities, just two levels, and great DLRS functionality OR I can have a limitless hierachy with Opportunities looking up to other Opportunities but am struggling to then achieve the desired ability to roll up budgeted and booked amounts.

Any advice about methods you've used to meet such a need? Any documentation you can point me toward?
Best Answer chosen by Nicole Zyvoloski
Amnon KruviAmnon Kruvi
Hi Nicole,.

I'm going to say something a bit outlandish here, it might be just what you need, or I may have completely misunderstood the issue:
Create an object called Opportunity Group.
Add a relation to opportunities so they can be related to a group (Opportunity Group)
Add a relationship from Opportunity Group to Opportunity Group (Parent Opportunity Group)
Using DLRS, create a summary field on opportunity group that sums the Amount of all child opportunities (Opportunity Value)
Using DLRS, create a summary field on opportunity group that sums the Value field of all child opportunity groups (Child Groups Value)
Create a formula field (Value) that performs Opportunity Value + Child Groups Value
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!
Robert ThomasRobert Thomas 
I got a question as mentioned and got the Options as below
a)Lead
b)Opportunity
c)Case
d)Account/Contact
e)Solution

And its mentioned Answer as d) Account/Contact

Can you tell me what this "Buisness Process" mentioned to?
I can see Validation Rules & Record Types for Account/Contact.

Can someone help me on this to understand this? 
Best Answer chosen by Robert Thomas
Pritam ShekhawatPritam Shekhawat
We are having a business process for these objects in Salesforce.
Lead, Opportunity , Case ,Solution

Some Points to consider for a business process :


You must create the business process before creating record types for each of above objects.
You can then associate each business process with one or more record types and make it available to users based on their profile.
 In order to implement more than one business process, multiple record types must also be implemented.

Business Process Examples
Lead Processes:

– 3rd Party Leads
– Leads generated via campaigns
– Leads generated via a registration form

Opportunities Sales Processes:

– Inside Sales vs. Outside Sales
– New business vs. Existing Business (Up selling)

Case Processes:
– Customer Inquiries
– Internal Requests
– Billing inquiries

Solutions Processes:
– Internal vs. Public Knowledge Base


And Account/Contact don't have any business process. 
Philipp MathisPhilipp Mathis 
hello community
I'm looking for a formula that returns a date in the future given a start date (manually to enter) plus a certain duration (in months). So e.g. the start date is today and in the term field I have 50months ->it should return me the exact date in the future (today + 50 months). As of now I only have a formula that gives me a rough estimate but is not considering the different leap years and is also not considering that month have differen amount of days. Is there a formula that properly caluclates that?

Many thanks for your help,
Philipp
Best Answer chosen by Philipp Mathis
Steve MolisSteve Molis
So you basically want this?
 
(ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
 +
FLOOR( 
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
DAY(ADDMONTHS(DATE(YEAR(Subscription_Start_Date__c),MONTH(Subscription_Start_Date__c), 01), Subscription_Term__c) - 1)
)) - 1

 
Chidubem Irenaeus ChukwuemekaChidubem Irenaeus Chukwuemeka 
Hello Everyone, 
........... it's been some hours of trial and it not really working. I'll be grateful if someone can help me solve these queries I've got with this challenge. 

thanks in advance.

CHUI
Lightning Experience Reports & Dashboards SpecialistLightning Experience Reports & Dashboards SpecialistLightning Experience Reports & Dashboards Specialist
Best Answer chosen by Chidubem Irenaeus Chukwuemeka
Tulika KhanduriTulika Khanduri

hey Chidubem Irenaeus Chukwuemeka.

here's the screenshot of mine, see if this could help User-added image