I have a form where submissions include two questions with 5 important levels and 5 options in each, and I want to see the weighted average of each option within each question
Question 1: What is most important to you?
Options: Support, Documentation, Quality, Patience, Speed
Most Important | Important | Neutral | Not Important | Least Important
Question 2: What is most important to you?
Options: Communication, Professionalism, Activity, Completion, Punctuality
Importance Most Important | Important | Neutral | Not Important | Least Important
Results table
| SubmissionDate | Question1A | Question1B | Question1C | Queston1D | Queston1E | Question2A | Question2B | Question2C | Queston2D | Question2E |
|---|
| 1/1/17 | Support | Documentation | Patience | Speed | Quality | Communication | Activity | Completion | Punctuality | Professionalism |
| 1/4/17 | Quality | Support | Documentation | Patience | Speed | Activity | Communication | Punctuality | Professionalism | Completion |
| 1/9/17 | Speed | Support | Quality | Documentation | Patience | Punctuality | Completion | Communication | Professionalism | Activity |
| 1/15/17 | Support | Quality | Patience | Speed | Documentation | Activity | Completion | Professionalism | Communication | Punctuality |
At the end of the day, I am trying to create a table that changes it's order based on the weighted average score.
| Support | Documentation | Quality | Patience | Speed |
|---|
| 3.8 | 3.2 | 2.3 | 2.1 | 1.8 |
I created an inline load for the two option sets:
Dim1:
LOAD * INLINE [
Dim1
Speed
Documentation
Patience
Support
Quality
];
Dim2:
LOAD * INLINE [
Dim2
Communication
Activity
Completion
Punctuality
Professionalism
];
In the table, the dimension is set as 'Dim1' and there are two expressions.
1. Unknown
2. RowNo()
Best expression I could come up with was something like this which didn't work.
If(Dim1='Speed',sum(if(Question1A='Speed',5,
if(Question1B='Speed',4,
if(Question1C='Speed',3,
if(Question1D='Speed'=2,
if(Question1E='Speed',1,0)))))
Thank you for the help,
Phil
