- Posts: 8
- Thank you received: 0

- Forum
- English support forums
- Can I do this with LimeSurvey?
- Javascript for input based formula calculation in matrix cells

# Javascript for input based formula calculation in matrix cells

1 year 1 month ago #185674
by DPetrik

Dear community,

i am currently trying to implement a calculation logic for the array (number) matrix question type, where the values for some cells (which are disabled for editing) are calculated automatically, based on the user input of other cells.

Here is a concrete example: If a user in a 2x2 matrix (AA, AB, BA, BB) enters "5" as a value in AB, the disabled counter cell BA should calculate a formula based on this input, such as "=1/5".

Does anyone have experience with this logic implementation in LimeSurvey? I would be very thankful for any suggestion or JS code snippet for further testing.

Thanks a lot in advance.

Kind Regards

DPetrik

i am currently trying to implement a calculation logic for the array (number) matrix question type, where the values for some cells (which are disabled for editing) are calculated automatically, based on the user input of other cells.

Here is a concrete example: If a user in a 2x2 matrix (AA, AB, BA, BB) enters "5" as a value in AB, the disabled counter cell BA should calculate a formula based on this input, such as "=1/5".

Does anyone have experience with this logic implementation in LimeSurvey? I would be very thankful for any suggestion or JS code snippet for further testing.

Thanks a lot in advance.

Kind Regards

DPetrik

The topic has been locked.

1 year 1 month ago - 1 year 1 month ago #185695
by Joffm

Replied by

*Joffm*on topic*Javascript for input based formula calculation in matrix cells*
Hi,

I do not think that you really need javascript. You can achieve this with built-in features (Expression Manager)

It is related to your other question, isn't it?

Well, you can disable the lower left triangle by just removing all "<options>" from the "select" of these cells.

A following hidden question of type equation fills an array with the calculated values (also hidden).

equation like:

{Result_Y001_X001=1}

{Result_Y002_X001=if(Q1f_Y001_X002=="0.1111",9,if(Q1f_Y001_X002=="0.1414",7,if(Q1f_Y001_X002=="0.2000",5,if(Q1f_Y001_X002=="0.3333",3,if(Q1f_Y001_X002=="1",1,if(Q1f_Y001_X002=="3",0.3333,if(Q1f_Y001_X002=="5",0.2000,if(Q1f_Y001_X002=="7",0.1414,0.1111))))))))}

{Result_Y001_X002=Q1f_Y001_X002}

{Result_Y002_X002=1}

Joffm

I do not think that you really need javascript. You can achieve this with built-in features (Expression Manager)

It is related to your other question, isn't it?

Well, you can disable the lower left triangle by just removing all "<options>" from the "select" of these cells.

A following hidden question of type equation fills an array with the calculated values (also hidden).

equation like:

{Result_Y001_X001=1}

{Result_Y002_X001=if(Q1f_Y001_X002=="0.1111",9,if(Q1f_Y001_X002=="0.1414",7,if(Q1f_Y001_X002=="0.2000",5,if(Q1f_Y001_X002=="0.3333",3,if(Q1f_Y001_X002=="1",1,if(Q1f_Y001_X002=="3",0.3333,if(Q1f_Y001_X002=="5",0.2000,if(Q1f_Y001_X002=="7",0.1414,0.1111))))))))}

{Result_Y001_X002=Q1f_Y001_X002}

{Result_Y002_X002=1}

Joffm

*Volunteers are not paid.*

Not because they are worthless, but because they are priceless

Not because they are worthless, but because they are priceless

##### Attachments:

Last edit: 1 year 1 month ago by Joffm.

The following user(s) said Thank You: DenisChenu

The topic has been locked.

1 year 1 month ago #185705
by DPetrik

Replied by

*DPetrik*on topic*Javascript for input based formula calculation in matrix cells*
Hi Joffm,

thank you very much for your reply. I am struggling with the expression manager and getting undefined variables. Could you kindly provide an export of an examplary lss file, which contains the implemented equation?

Thank you a lot in advance.

Kind Regards

Dimitri

thank you very much for your reply. I am struggling with the expression manager and getting undefined variables. Could you kindly provide an export of an examplary lss file, which contains the implemented equation?

Thank you a lot in advance.

Kind Regards

Dimitri

The topic has been locked.

1 year 1 month ago #185706
by Joffm

Replied by

*Joffm*on topic*Javascript for input based formula calculation in matrix cells*
Hi,

here an example.

1. I used the array(numbers) with text input.

2. I changed all "select" to "input" in the first part of the javascript.

3. It is not mandatory to fill all cells. If you want to change it to mandatory, you have to validate the question.

Original question with some empty fields

Result (which should be hidden in your real survey)

Here the survey.

Remember: Check the hardcoded SGQ ([SurveyID]X[GroupID]X[QuestionID]) in the javascript. It should be adapted but who knows...

Joffm

here an example.

1. I used the array(numbers) with text input.

2. I changed all "select" to "input" in the first part of the javascript.

3. It is not mandatory to fill all cells. If you want to change it to mandatory, you have to validate the question.

Original question with some empty fields

Result (which should be hidden in your real survey)

Here the survey.

Remember: Check the hardcoded SGQ ([SurveyID]X[GroupID]X[QuestionID]) in the javascript. It should be adapted but who knows...

Joffm

*Volunteers are not paid.*

Not because they are worthless, but because they are priceless

Not because they are worthless, but because they are priceless

##### Attachments:

The following user(s) said Thank You: DPetrik

The topic has been locked.

1 year 1 month ago #185719
by DPetrik

Replied by

*DPetrik*on topic*Javascript for input based formula calculation in matrix cells*
Dear Jeffm,

thank you for your great help! I am so excited to see the working results, which would never be possible without your support!

Now i am even not sure, whether the second (calculated) matrix should be hidden or either enable a consistency self-check for the survey participants

Anyway great thanks for your help. I am really appreciating it. I am planing to conduct a survey for my dissertation and this technical puncture was a great milestone, to replace prototypical excel sheets

I still have one more short question. As the survey is not live, i was not able to check what is practically stored in the database - the results from both matrices (questions 1+3)?

Kind Regards

Dimitri

P. S. Your signature is a program

thank you for your great help! I am so excited to see the working results, which would never be possible without your support!

Now i am even not sure, whether the second (calculated) matrix should be hidden or either enable a consistency self-check for the survey participants

Anyway great thanks for your help. I am really appreciating it. I am planing to conduct a survey for my dissertation and this technical puncture was a great milestone, to replace prototypical excel sheets

I still have one more short question. As the survey is not live, i was not able to check what is practically stored in the database - the results from both matrices (questions 1+3)?

Kind Regards

Dimitri

P. S. Your signature is a program

The topic has been locked.

1 year 1 month ago #185722
by Joffm

Everything is stored, even the results of the equation.

Or, if you want to show, use a question of type "textdisplay" and display the values in a grid there.

Now there is one question that came to my mind.

Do you use the calculated values later in your survey?

Like in a later question

"You entered a value of {Frage1R_SQ005_SQ001} for 'DevOps Metrics -> APIs'.

Please, explain in a few words, why."

If not, everything here was "l'art pour l'art" and you could have calculated these values in your statistic tool.

Nevertheless, it's a process of learning.

Best regards

Joffm

Replied by

*Joffm*on topic*Javascript for input based formula calculation in matrix cells*Why not activate your small example as open survey, enter data, and have a look at the answer table? Then deactivate again.As the survey is not live, i was not able to check what is practically stored in the database - the results from both matrices (questions 1+3)?

Everything is stored, even the results of the equation.

Remember, if you show, participant may enter new data, or you have to disable all cells of this question, too.enable a consistency self-check for the survey participants

Or, if you want to show, use a question of type "textdisplay" and display the values in a grid there.

Now there is one question that came to my mind.

Do you use the calculated values later in your survey?

Like in a later question

"You entered a value of {Frage1R_SQ005_SQ001} for 'DevOps Metrics -> APIs'.

Please, explain in a few words, why."

If not, everything here was "l'art pour l'art" and you could have calculated these values in your statistic tool.

Nevertheless, it's a process of learning.

Best regards

Joffm

*Volunteers are not paid.*

Not because they are worthless, but because they are priceless

Not because they are worthless, but because they are priceless

The topic has been locked.

1 year 1 month ago #185900
by Joffm

Replied by

*Joffm*on topic*Javascript for input based formula calculation in matrix cells*
Hi, Dimitri,

please see this remark of tpartner about disabling inputs.

https://www.limesurvey.org/forum/can-i-do-this-with-limesurvey/118670-how-to-make-one-column-in-an-array-text-question-readonly#185887

Joffm

please see this remark of tpartner about disabling inputs.

https://www.limesurvey.org/forum/can-i-do-this-with-limesurvey/118670-how-to-make-one-column-in-an-array-text-question-readonly#185887

Joffm

*Volunteers are not paid.*

Not because they are worthless, but because they are priceless

Not because they are worthless, but because they are priceless

The topic has been locked.

1 year 1 month ago #186044
by DPetrik

Replied by

*DPetrik*on topic*Javascript for input based formula calculation in matrix cells*
Dear Jeffm,

thank you very much for this suggestion. I will definitely test it out. I have to dig deeper into the practiced ways of how to conduct a survey for AHP. Maybe a read-only check up is a really good improvement.

However, AHP also requires a consistency check, which is based on several formulas in the background. I would like to talk with my collegueas first, who are experts into this method, whether the consistency check should also be implemented in the survey.

If it should, the consistency check could use the calculated values to calculate:

1. Normalized values of the whole matrix

2. Sum of each row of the normalized matrix

3. Average by dividing the sum (2) by the Average of each Row

4. Average of all the average values (3)

5. Consistency Index = ( Result from step 4 - Number of Criteria)/(Number of Criteria - 1)

6. Divide the consistency index through the random index (random index = number of items compared in the matrix)

7. Compare whether the consistency index is < 0,1. If it is it should warn the user to check his input one more time.

That will be a whole lotta' fun

Kind Regards

thank you very much for this suggestion. I will definitely test it out. I have to dig deeper into the practiced ways of how to conduct a survey for AHP. Maybe a read-only check up is a really good improvement.

However, AHP also requires a consistency check, which is based on several formulas in the background. I would like to talk with my collegueas first, who are experts into this method, whether the consistency check should also be implemented in the survey.

If it should, the consistency check could use the calculated values to calculate:

1. Normalized values of the whole matrix

2. Sum of each row of the normalized matrix

3. Average by dividing the sum (2) by the Average of each Row

4. Average of all the average values (3)

5. Consistency Index = ( Result from step 4 - Number of Criteria)/(Number of Criteria - 1)

6. Divide the consistency index through the random index (random index = number of items compared in the matrix)

7. Compare whether the consistency index is < 0,1. If it is it should warn the user to check his input one more time.

That will be a whole lotta' fun

Kind Regards

The topic has been locked.

1 year 1 month ago #186050
by Joffm

Replied by

*Joffm*on topic*Javascript for input based formula calculation in matrix cells*
Oh, interesting.

LimeSurvey doesn't know skips, neither forward nor backward.

If there were skips you could calculate the few values which is easy (only a bit summing, counting and dividing) and skip back to the question of the condition is not fulfilled.

If, yes, if.

The only way IMO is to show an incremental index and ask the respondent to go back to the question.

Disadvantage: The old values are not saved. The respondent has to enter everything again.

Or you throw away everything and try to do it with javascript within the question.

Best regards

Joffm

P.S.

Are you sure?

LimeSurvey doesn't know skips, neither forward nor backward.

If there were skips you could calculate the few values which is easy (only a bit summing, counting and dividing) and skip back to the question of the condition is not fulfilled.

If, yes, if.

The only way IMO is to show an incremental index and ask the respondent to go back to the question.

Disadvantage: The old values are not saved. The respondent has to enter everything again.

Or you throw away everything and try to do it with javascript within the question.

Best regards

Joffm

P.S.

Are you sure?

This is sum/(sum/count) = countAverage by dividing the sum (2) by the Average of each Row

*Volunteers are not paid.*

Not because they are worthless, but because they are priceless

Not because they are worthless, but because they are priceless

The topic has been locked.