Question T17303
Visible to All Users

Gather data for column4 and column5, then calculate and set the value for column6

created 10 months ago

Hi, I'm trying to gather the data for column4 and column5 of a question, multiply it, and set the product as the answer/value for column6. I want to do this FOR EACH row of the question. Whenever I use the Conditions to set value expression using the surveyjs website, all the values of column6 of EACH ROW are the same value of the column6 from the very first row, So I'm trying to achieve this dynamically by coding instead.

JSON
"type": "matrixdynamic", "name": "riskIdentificationBefore", "title": "Risk Identification Before Mitigation", "description": "Please number each possible consequence of privacy risks", "isRequired": true, "columns": [ { "name": "Column 1", "title": "Data Subject(s) who are Impacted", "cellType": "comment", "isRequired": true }, { "name": "Column2", "title": "Possible Consequence of Privacy Risk to the Data Subject/s", "cellType": "comment", "isRequired": true, "placeholder": "Identify all possible risks for each stage in the personal data life cycle. " }, { "name": "Column3", "title": "Type Of Threat", "cellType": "checkbox", "colCount": 0, "isRequired": true, "choices": [ { "value": 1, "text": "Confidentiality" }, { "value": 2, "text": "Integrity" }, { "value": 3, "text": "Availability" }, { "value": 4, "text": "Unauthorized" }, { "value": 5, "text": "Violation" } ], "storeOthersAsComment": true }, { "name": "Column4", "title": "Severity", "cellType": "radiogroup", "colCount": 0, "isRequired": true, "choices": [ 1, 2, 3, 4 ], "storeOthersAsComment": true }, { "name": "Column5", "title": "Likelihood", "cellType": "radiogroup", "colCount": 0, "isRequired": true, "choices": [ 1, 2, 3, 4 ], "storeOthersAsComment": true }, { "name": "Column6", "title": "Risk Rating", "cellType": "comment", "isRequired": true, "placeholder": "Risk Rating = Severity x Likelihood" } ], "choices": [ 1, 2, 3, 4, 5 ], "rowCount": 1 },

Then I tried using different javascript jquery methods:

JavaScript
function updateRiskRating(survey) { var question = survey.getQuestionByName("riskIdentificationBefore"); question.onPropertyChanged.add(function (sender, options) { if (options.name === "Column4" || options.name === "Column5") { // iterate through each row and update the value of Column6 sender.iterate(function (result) { var col4Value = result.getProperty("Column4"); var col5Value = result.getProperty("Column5"); var col6Value = col4Value * col5Value; result.setProperty("Column6", col6Value.toString()); }); // refresh the question to apply the changes sender.refresh(); } }); }

OR

JavaScript
function calculateRiskRating(survey) { const questionName1 = "riskIdentificationBefore"; const questionName2 = "riskIdentificationAfter"; const riskIdentificationQuestion1 = survey.getQuestionByName(questionName1); const riskIdentificationQuestion2 = survey.getQuestionByName(questionName2); const severityData1 = (riskIdentificationQuestion1.columns[3].choices || []).map(choice => ({ severity: (choice || {}).text || "", value: parseInt(choice.value) || 0 })); const likelihoodData1 = (riskIdentificationQuestion1.columns[4].choices || []).map(choice => ({ likelihood: (choice || {}).text || "", value: parseInt(choice.value) || 0 })); const severityData2 = (riskIdentificationQuestion2.columns[3].choices || []).map(choice => ({ severity: (choice || {}).text || "", value: parseInt(choice.value) || 0 })); const likelihoodData2 = (riskIdentificationQuestion2.columns[4].choices || []).map(choice => ({ likelihood: (choice || {}).text || "", value: parseInt(choice.value) || 0 })); // Extract rows data with proper x and y values for the first risk map const rowsData1 = riskIdentificationQuestion1.visibleRows.map((row, index) => { const rowValue = riskIdentificationQuestion1.getRowValue(index); // Get the data for Column4 and Column5 from the survey data const xValue = rowValue['Column5'] || 0; const yValue = rowValue['Column4'] || 0; // Ensure xValue and yValue are numbers return { number: index + 1, xValue: isNaN(xValue) ? 0 : Number(xValue), yValue: isNaN(yValue) ? 0 : Number(yValue), }; }); // Extract rows data with proper x and y values for the second risk map const rowsData2 = riskIdentificationQuestion2.visibleRows.map((row, index) => { const rowValue = riskIdentificationQuestion2.getRowValue(index); // Get the data for Column4 and Column5 from the survey data const xValue = rowValue['Column5'] || 0; const yValue = rowValue['Column4'] || 0; // Ensure xValue and yValue are numbers return { number: index + 1, xValue: isNaN(xValue) ? 0 : Number(xValue), yValue: isNaN(yValue) ? 0 : Number(yValue), }; }); // Calculate the risk rating for each row in the first risk map const riskRatingData1 = rowsData1.map(row => { const severityChoice = severityData1.find(choice => choice.value === row.xValue); const likelihoodChoice = likelihoodData1.find(choice => choice.value === row.yValue); const risk = severityChoice.value * likelihoodChoice.value; return { ...row, riskRating: risk, }; }); // Calculatethe risk rating for each row in the second risk map const riskRatingData2 = rowsData2.map(row => { const severityChoice = severityData2.find(choice => choice.value === row.xValue); const likelihoodChoice = likelihoodData2.find(choice => choice.value === row.yValue); const risk = severityChoice.value * likelihoodChoice.value; return { ...row, riskRating: risk, }; }); return { question1: { title: questionName1, data: riskRatingData1, }, question2: { title: questionName2, data: riskRatingData2, }, }; }
Comments (2)

    image_2024-03-18_101937873.pngIf it helps, this is the structure of the question:

      Also, I can't change the structure of the question/rows/columns because of this:
      https://surveyjs.answerdesk.io/ticket/details/t17081

      If I change it, I have to re-do everything all over again

      Answers approved by surveyjs Support

      created 10 months ago

      Hi Kyle,
      You can achieve the target output using survey expressions. To multiple matrix columns and save the multiplication result within another column, define a column's a defaultValueExpression and use the {row.columnName} placeholders to reference current row values.

      So, to calculate the Risk Rating as a multiplication of Severity and Likelihood columns, please consider the following matrix configuration.

      JSON
      { "pages": [ { "name": "page1", "elements": [ { "type": "matrixdynamic", "name": "riskIdentificationBefore", "title": "Risk Identification Before Mitigation", "description": "Please number each possible consequence of privacy risks", "isRequired": true, "columns": [ { "name": "Column 1", "title": "Data Subject(s) who are Impacted", "cellType": "comment", "isRequired": true }, { "name": "Column2", "title": "Possible Consequence of Privacy Risk to the Data Subject/s", "cellType": "comment", "isRequired": true, "placeholder": "Identify all possible risks for each stage in the personal data life cycle. " }, { "name": "Column3", "title": "Type Of Threat", "cellType": "checkbox", "colCount": 0, "isRequired": true, "choices": [ { "value": 1, "text": "Confidentiality" }, { "value": 2, "text": "Integrity" }, { "value": 3, "text": "Availability" }, { "value": 4, "text": "Unauthorized" }, { "value": 5, "text": "Violation" } ], "storeOthersAsComment": true }, { "name": "Column4", "title": "Severity", "cellType": "radiogroup", "colCount": 0, "isRequired": true, "choices": [ 1, 2, 3, 4 ], "storeOthersAsComment": true }, { "name": "Column5", "title": "Likelihood", "cellType": "radiogroup", "colCount": 0, "isRequired": true, "choices": [ 1, 2, 3, 4 ], "storeOthersAsComment": true }, { "name": "Column6", "title": "Risk Rating", "cellType": "comment", "isRequired": true, "defaultValueExpression": "{row.Column4} * {row.Column5}", "placeholder": "Risk Rating = Severity x Likelihood" } ], "choices": [ 1, 2, 3, 4, 5 ], "rowCount": 1 } ] } ], "showQuestionNumbers": "off", "questionTitleLocation": "left" }

      Clipboard-File-2.png

      Please let me know if this configuration works for you.

        Comments (2)

          Thank you so much! It works now! Didn't know it was that simple lol

            You are always welcome, Kyle. Refer to the following demo for more examples on using expressions and matrices: Aggregare Data within a Form.

            Thanks