I am trying to create a custom variable that creates an average score across item responses. I can set this up with a formula. The issue, however, is that the formula doesn't adjust depending on whether all of the items are answered by the responded. For example, I'm trying to create an average score for dimension A that is made up of items 1-5. The formula is item1 + 2 + 3 + 4 + 5 / 5. However, if a respondent fails to answer one item, the denominator should shift to 4. Is there a way to write the formulate to capture this dynamically?
Question
How do I create a custom variable to create an average score?
+18Hi FDG2102 ,
As of now you can not have dynamic formulation as required in your case in Qualtrics. However I would like to suggest one approach to export and delete (to avoid duplication of record) the data in excel, then do the average calculation in excel and import the data again.
+38FDG2102 you can use scoring to accomplish this - it can be set up before or after fielding. And will be applied retro actively. The only problem is that the only thing that exports and appears in the report is Score (sum of values). You need access to the WeightedMean value.
To have access to the average you are looking for, create an Embedded Data field that saves in the piped value Weighted Mean. To make sure the data records you will want to make sure this is set up before fielding. Embedded Data may not be retroactive like scoring is.
Here is the help page on how to set up scoring: https://www.qualtrics.com/support/survey-platform/survey-module/survey-tools/scoring/
Hi @fdg2102, @bstrahin and @SaurabhPujare_Ugam
I've just come across this thread as I was after the same thing. I was able to create a custom field (of the variable type) to do this with some clever maths.
My test was to have 4 fields with answer values 1-5. Some fields are not answered. The fields are v, w, x, y. The formula below should be use to work out the number of answered fields:
(4-floor(0.1^v + 0.1^w + 0.1^x + 0.1^y)
This works as per the below picture. The formula is in the far right column.
Once this is working as expected then you can do a simple adding of the fields and divide it by this figure to work out the average.
So the resulting formula is:
(v + w + x + y ) / (4-floor(0.1^v + 0.1^w + 0.1^x + 0.1^y))
My plan is to use this in a dashboard simple widget using an average metric. It is in some senses an average of an average which might not be entirely best practise but it should give a value you can compare over time. I set it up as a number field in the dashboard settings page and as a consequence it can't be used as a star rating widget but that's fine for my needs.
Here is the resulting chart. The black line is the average. So it looks like it's working!! :)
Hope that helps
Thanks
Rod Pestell
PS yep - creating a number set verison in the dataset settings of the dashboard and using a star rating widget doesn't really word!! lol😂
PPS wish we could remove the 0 stars as the scale is 1-5.
Rod_Pestell
I am confused, where are you utilizing your formula, in the widget, or somewhere else? You said "I was able to create a custom field (of the variable type)" which implies to me you used an embedded data field. Can you give me more information about your solution?
Thanks
Hi cess, (sorry for the delayed reply - I'm furloughed most of the week at present). You create a custom field in the Survey > Data & Analysis section (the pictures above show this - the grid of data - what I call the raw data page). In there you go to Tools > Create New Field. In the 'Create variable' window, click on 'Formula' and follow the above instructions. You might need to change the formula based on how many fields you're wanting to average.
Hope that helps
Thanks
Rod Pestell
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.