Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Michael FaberMichael Faber 
I am trying to write a report formula that will return the % of one option from a picklist.

Lets say we have 10 items returned in an activity report, and we are grouping by Type (picklist) and Sub Type (picklist). The Sub Type picklist contains options Inquiry and Transfer

We are trying to create a column that will return the percentage of rows that have selected Sub Type Transfer, irrespective of Type.

User-added image

Is this possible without configuration changes?
Best Answer chosen by Michael Faber
Steve MolisSteve Molis
These are the Formulas that I used (keep in mind I uswed an Opportunity Report in my example to approximate the data)

Formula A
IF(PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY) = 0, 0 ,
RowCount
 / 
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY)
)

Formula B
IF(PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY) = 0, 0,
RowCount / 
PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY))
Formula C 
IF( PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c) = 0, 0, 
RowCount / 
PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c))


​​​​​​​
David NavaDavid Nava 
Background
I created a custom object in Salesforce Lightning to record survey results. The fields on the survey object that record the respondent's answers are all picklists. All picklists reference the same global value set. The global value set is a Likert Scale, as follows:
5-Very High Confidence
4
3-Confident
2
1-Very Low Confidence

The survey object has master detail relationships to the Contact and Campaign records.
I created a custom Contacts with Surveys and Campaigns report.
There are twelve questions on the survey that need to be reflected in the report that I am trying to create.

Requirement
I need the report to list each of the twelve questions. This is easy and already accomplished. I also need the report to calculate the average value of each of the question columns and return a summary value. For example, column one lists the Likert Scale results for each user for Question 1. I need the values for the Likert Scale responses to be averaged for that column, and for the next 11 columns (one column per survey question). See below for a screenshot of my report as currently structured.
Survey Report Snapshot

Problem
This is where I am struggling. I can't figure out how to summarize each of the question columns. My company want to be able to review the collective results from the survey and be able to immediately tell which questions they scored low in,out of the 12 questions. A chart showing thjs would be awesome.

I can't gfigure out how to do this. Is a formula column or columns the way to go? 
Pleas help. Thank you in advance!!!

Best,
Dave Nava
Awesome Admin
Best Answer chosen by David Nava
Sunil SarillaSunil Sarilla
Hi David,
I don't think you will be able to summarize the report by 12 different fields and show it on a chart with the way your data is structured.
whats possible is showing the avg value for each question in a campaign.
so you will need 12 charts i.e a chart for each question and then show the information in a dashboard but this is not scalable if you want to show the avg scores for each question for a different campaign.
or a Lightning Table component Dashboard

In order to summarize by the question, you will need to convert the picklist field into a numeric value, so you will need a formula field (12 formula fields)
the formula will be as below
CASE(Your_Response_Picklist_Field__c,
'5-Very High Confidence',5,
'4',4,
'3-Confident',3
'2',2,
'1-Very Low Confidence',1,
0)
 
How flexible are you in changing the data structure?
Steve MolisSteve Molis 
Here's a Formula I built to create an "Ultimate Parent Account" field that you can use to create Opportunity Pipeline reports that roll up all Opportunities under the top Account in the hierarchy. 

*** in this example I'm testing up to a 5 Tier Account Hierarchy (Compiled size: 342 characters) ***

Datatype: Formula 
Result: TEXT 
Formula: 
 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name,
Name)))))

 
Best Answer chosen by Steve Molis
Jeremiah DohnJeremiah Dohn
Marrying Ultimate Parent ID + Ultimate Parent Name with levels can create some great reporting.  With the below, you can see all levels of the hierarchy and what they are bringing in from a forecast perspective.

Ultimate Parent ID: 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Name, 
BLANKVALUE(Parent.Name, 
Name)))))

All hierarchy level names:
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)),
Parent.Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name & " - " & Name,
Name)))))
Note that we can't use BLANKVALUE as the base for the formula because "-" would be not blank for each level. Thus using the nested IF().  I'm sure we can optimize all the same to reduce compile size.

User-added image
Aniqa MoinuddinAniqa Moinuddin 
Does anyone know how I can define a formula that returns Monday/Tuesday/Thursday etc from a date field? The only formula I can think of requires that I define a variable ( Sunday = Day (Date expression) + 7x; where x= integer between 0 and infinity)

Thanks!
Best Answer chosen by Moderator (salesforce.com) 
Sami EllongSami Ellong
Try this:

CASE( MOD( Date__c - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3,
"Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
Samantha MillerSamantha Miller 
Hi!
I'm looking to create a formula in a report for a dashboard to calculate lead velocity,  which is the following formula:

User-added image

I can't figure out how to get that correct in the report to show up as a % in a dahsboard. Any help for how to group fields and create this formula?

Thanks! 
Best Answer chosen by Samantha Miller
Mike BrownMike Brown
Hi Samantha, 

I think the challenge here is that if a lead was qualified last month, it would still be considered qualified in the current month. I think you need to first have a Lead Qualified Date field. This should be a date that gets stamped with the moment the lead was qualified.  That way, when viewing leads in reports, you can see when leads were qualified historically. You could use Process Buiilder to help populate this field whenever the Lead meets your criteria for being considered qualified, such as when it enters a certain Status.

Then, in your report, you should be able to run a report where the report is filtered to show Leads where Lead Qualified Date = Current Month or Previous Month. Then, you should be able to calculate the Lead Velocity you are looking for by leveraging the PREVGROUPVAL formula. I think your formula would look like something like:
((RowCount - PREVGROUPVAL(RowCount, LEAD_QUALIFICATION_DATE)) / PREVGROUPVAL(RowCount, LEAD_QUALIFICATION_DATE)) * 100
Just let me know if any of that doesn't make sense or if you would need help setting that up. 

Thanks!
Mikey

 
Sridevi KaruppannanSridevi Karuppannan 

Uncaught Error: Invalid string length RangeError: Invalid string length at t.prepareRender (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1280078) at t.prepareRender (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1251812) at t._actualRender (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1369168) at https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1368284 at Xl (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1371758) at t._renderNow (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1368261) at t._flushAccumulatedAndRenderNow (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1365341) at t._onRenderScheduled (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:1368183) at e.execute (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:70924) at z (https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:71112) throws at https://empathetic-impala-tbdobd-dev-ed.lightning.force.com/projRes/insights/gen/static/reports/js/report.common.chunk.cae2578e39ec35256064.js:1:80615
Best Answer chosen by Sridevi Karuppannan
Sridevi KaruppannanSridevi Karuppannan
Hi Shiv, thanks for your quick  reply. I tried that too. But finally I found the solution that only that error comes when I try to click the old logic and type the new one, in edit mode. So avoiding this, I deleted entire previous logic rules and then tried to type the new one. Now I passed the challenge 😊 how is that .
Rohini KondreddyRohini Kondreddy 
Hello everyone,

We have a custom object Quote Request with Status as a picklist field. We need to create win/loss ratio report on it. 

I have  created bucket fields on the status to achieve this.
Won (picklist values -  PO received/Complete ) 
Loss (with the picklist values as declined)
Closed (picklist value - closed)
I can't use these bucket fields in the summary formula. How can I achieve the win/loss ratio?

Ideas are much appreciated!

Thanks
Rohini
 
Best Answer chosen by Rohini Kondreddy
Steve MolisSteve Molis
Are they mutually exclusive?  Or are some of the Picklist values in more than 1 Bucket? 

Again, I would replace the Report Buckets with custom Formula(Checkbox) or Formula(Number) fields and use them in your Report Summary Formula like this 
 
( Quote.Won__c:SUM / Quote.Closed__c:SUM )

 
Anand SubramanianAnand Subramanian 
Hi,
I went to SetUp->Data Export->Schedule Data Export. However I dont see the option to schedule weekly. Is there somethingn I am missing here?

Data Export Scheduling
Best Answer chosen by Anand Subramanian
Steve MolisSteve Molis
PS.  Winter'18 is not an "editon" of Salesforce, it is a release.  Your edition of SF does not support weekly data backup

DOCUMENTATION
Export Backup Data from Salesforce 

Available in: both Salesforce Classic and Lightning Experience
Weekly export available in: Enterprise, Performance, and Unlimited Editions
Monthly export available in: All editions, except for Database.com
Mary O'DonovanMary O'Donovan 

I am getting stuck on the hands on challange Customize Record Highlights with Compact Layouts, getting error message Challenge Not yet complete... here's what's wrong: Could not find a compact layout with the label 'New Oppty Compact Layout'.

challange is
Create a custom Opportunity compact layout
When the Ursa Major Solar salespeople are on site with a customer, there are a few key fields they need to see right at the top of an opportunity record when they access Salesforce. Create a compact layout that will help them do that.

Create a new compact layout for the Opportunity object with the label New Oppty Compact Layout
Include these fields, in this order: Opportunity Name, Probability (%), Close Date, Stage, Amount, Opportunity Owner
Make it the primary compact layout

I know i am proberly missing something quiet basic but cannot see what it is.
Thanks for some help

 

Best Answer chosen by Ed (salesforce.com) 
Ryan WayRyan Way
Ms White, 
I did the same thing! You are in the wrong area though, that was where you needed to be for the example Create a Compact Layout section. For the challenge, everything is about the same except this:

User-added imageUser-added image

Hope that helps ! 
Ranveer BarooahRanveer Barooah 

Is there a way I can possibly add a variable Baseline/Target line within a Column/Bar Graph that's displaying some Data Sets...? Using CSF in the Report I believe it's not possible as the Target is variable and not a static one hence, the line would not be straight and rather it will a fluctuated one!The red line that I want to represent as a Target LineThe second instance through a Line Graph

Please advise how this can be achieved. Tagets are not captured as a field so need to perform the customization in relation to the report!
Best Answer chosen by Moderator (salesforce.com) 
David CarnesDavid Carnes
Hi Ranveer,

Salesforce does not give us any way of setting target amounts within Reports or Dashboards (save for gas gauges).  One way that we've been able to get close to what you are asking for is to leverage Record Types (available in EE, UE) and store target records within the same Object that you are comparing against.  For example, if we have two Opportunity Record Types (Standard and Target), and all records must have an Owner, Close Date, Amount, and Account (the Account the Target record is associated with can be used or ignored for reporting purposes).  This would allow us on a Report to group by Record Type and show both on the same Report with a timeline (Create Date or Close Date), and y axis value (Record Count or Amount), with both target and actual on the same chart.

A side benefit of leveraging Record Types here is that we can lock down the page layout for the Target Record Type.  When doing this we'll often set the Org Wide Defaults on the Opportunity Object to Private, allowing only the owner and those above the owner in the Role Hierarchy to see the Targets.  Generally we'll also make all the fields read only and remove any delete capability on the Target records, so that only a sales ops or finance team member can adjust the Target records. 

Best of luck!

David Carnes
OpFocus, Inc.
www.opfocus.com/blog
@opfocusinc