Calculate mean based on recoded values for survey question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a database about the results of a survey and two of those tables in the database are:

(1) Response - it keeps students' responses, question ID, year of responses submitted, etc

(2) Option - it keeps the attributes about question's options, like the text of option, value, etc

For explanation, I have exported some data from these two tables to Excels and prepare a Qlikview (Files are attached).

The "Response" in Response table stores what students answered and the "QO_Key" in both tables link up student's response with the options.

Now I want to create a chart showing the mean of a question. Instead of using the value stored in "Response" field to calculate (which ranges from 1 to 5), I would like to convert it into a scale of 0 to 100 (i.e. 1=>0; 2=>25; 3=>50; 4=>75; 5=>100). This mapping relationship is defined in the field "Option_Calculation" in Option table.

How can I calculate the mean using a scale of 0 to 100 in Qlikview? I tried using dual function before in data loading script, but it seems that it does not work (or maybe I did not use it correctly). Thanks.

topic Calculate mean based on recoded values for survey question in QlikView

Calculate mean based on recoded values for survey question