Need Help with Reports: Creating Formula Column to Summarize Likert Scale Picklist Field Values - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
David NavaDavid Nava 

Need Help with Reports: Creating Formula Column to Summarize Likert Scale Picklist Field Values

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?

All Answers

Sunil SarillaSunil Sarilla
Hi Dave,
Each Question is a field on the object and each response is stored in the respective picklist field i.e. you have 12 questions and 12 picklist fields for responses
now you want to summarize the report by each question?
 
David NavaDavid Nava
Yes, that is correct. All the picklists are the same global value, the 1-5 Likert Scale.
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?
This was selected as the best answer
David NavaDavid Nava
The object was just created and has no data. I can definitely change the field type. My only constraint is that Form Assembly has to map to the object. That is how I am getting all of my data: students are filling out the Form Assembly form, which connects to the object and uploads all the data. So I can't change the field type to something that Form Assembly can't map to.
David NavaDavid Nava
So should I use the formula you suggested, or convert the fields to numeric fields and then add extra formula fields to the object to summarize the numeric responses?
David NavaDavid Nava
Is there a way to use rollup summary fields to get the result I want?
David NavaDavid Nava
Sunil,

Changing the field type to a number enabled me to summarize each column, which is exactly what I wanted, thank you!

Best,

Dave
vordiharzo biyacvordiharzo biyac
Restbet, Tüm oyuncuların korkulu rüyası olan tek maçtan dolayı yatan kuponlara son veriyor. En az 5 müsabaka olan kuponlarda artık tek maç tutmasa bile, şirket sizi kazanmış sayıyor ve kazancınızı hemen bakiyenize yatırıyor.