# how do I integrate this calculation?

6 years 5 months ago #80888
obiwancupcoffee created the topic: how do I integrate this calculation?
I've been playing around with LimeSurvey for over a month now, I've watched every available tutorial video and read loads of wiki's and forum posts, but I just can't figure it out.

I can do magic with MS Excel, but my programming skills are very limited. I can do some basic HTML, but I have no experience whatsoever with Java Script.
Maybe that is why I can't figure out how to do this in LimeSurvey.

What I need is to make an online survey of the setup outlined below.

Doing this in traditional question pipelining is too elaborate since that would require 2*6*6*6*6=2592 potential outcomes to be condensed back into 9 main outcomes manually.
So I've made a calculation for this in MS Excel. But I can't figure out how to transform that into a calculation for LimeWire's Expression Manager, since the current tutorials on Expression Manager are very limited and very complex.

Can anyone help me please ?

SURVEY OUTLINE

question 1:
A- male
B- female

question 2:
which fruit juice flavor do you like most?
[please rate 'preferably', 'okay' and 'rather not']
A- apple
B- orange
C- grape

question 3:
which fruit juice flavor does your partner like most?
[please rate 'preferably', 'okay' and 'rather not']
A- apple
B- orange
C- grape

question 4:
which fruit juice flavor do your children like most?
[please rate 'preferably', 'okay' and 'rather not']
A- apple
B- orange
C- grape

question 5:
which one fruit juice flavor would you consider your ultimate compromise?
A- pure apple
B- pure orange
C- pure grape
D- apple-orange mix
E- orange-grape mix
F- apple-grape mix

question 6:
A- true
B- not true

in question 6, what "flavor X" is, is based on the answers on questions 1, 2, 3, 4 and 5 and can be one of the following:
1- pure apple
2- pure orange
3- pure grape
4- apple with a little hint of orange
5- apple with a little hint of grape
6- orange with a little hint of apple
7- orange with a little hint of grape
8- grape whit a little hint of apple
9- grape with a little hint of orange

(I have a calculation for this in MS Excel, so it should be automated)

question 7:
[this question will only be shown if the answer to question 6 was 'not true']
A- pure apple
B- pure orange
C- pure grape
D- apple with a little hint of orange
E- apple with a little hint of grape
F- orange with a little hint of apple
G- orange with a little hint of grape
H- grape whit a little hint of apple
I- grape with a little hint of orange

6 years 5 months ago #80889 by TMSWhite
TMSWhite replied the topic: how do I integrate this calculation?
Please paste the Excel equation(s) you use.

Also, it's "LimeSurvey". LimeWire is an outlawed peer-to-peer file sharing software which is totally unrelated to LimeSurvey.

6 years 5 months ago #80929
obiwancupcoffee replied the topic: how do I integrate this calculation?
thank you for the reply TMSWhite
I'm sorry for the typo

CALCULATION

basic elements :

M = Male
F = Female

A = Apple
O = Orange
G = Grape

(these can be done using some SUBSTITUTE formulas, but are even easier using a LOOKUP formula and a conversion table, because of the limited amount of options)

in Question 1:

A = M
B = F

in Question 2, 3 & 4:

ABC = AOG = AAO = AAO
ACB = AGO = AAG = AAO
BAC = OAG = OOA = AOO
BCA = OGA = OOG = OOG
CAB = GAO = GGA = AGG
CBA = GOA = GGO = OGG

in Question 5:

A = AAAA
B = OOOO
C = GGGG
D = AAOO
E = OOGG
F = AAGG

then lineup the answers to all questions, e.g.:

(B +) ABC + BAC + CBA + E = (F +) AAO + AOO + OGG + OOGG = (F)AAOAOOOGGOOGG = (F)AAAOOOOOOGGGG = (F)OOG

(A +) CBA + CAB + CAB + C = (M +) OGG + AGG + AGG + GGGG = (M)OGGAGGAGGGGGG = (M)AAOGGGGGGGGGG = (M)GGG

so because of the gender digit, there are actually 2*9=18 possible outcomes (instead of the just 9 mentioned before), but the gender digit is best left out until just before finishing off

that last reduction calculation cannot be done using a conversion table, because of the extreme amount of options, but it can be done using a formula like this:
IF(LEN(SUBSTITUTE(X2;"A";""))<5;"AAA";IF(LEN(SUBSTITUTE(X2;"O";""))<5;"OOO";IF(LEN(SUBSTITUTE(X2;"G";""))<5;"GGG";
IF(AND((LEN(SUBSTITUTE(X2;"A";""))<(LEN(SUBSTITUTE(X2;"O";"")));(LEN(SUBSTITUTE(X2;"O";""))<(LEN(SUBSTITUTE(X2;"G";"")));"AAO";
IF(AND((LEN(SUBSTITUTE(X2;"A";""))>(LEN(SUBSTITUTE(X2;"O";"")));(LEN(SUBSTITUTE(X2;"O";""))>(LEN(SUBSTITUTE(X2;"G";"")));"OGG";
IF(AND((LEN(SUBSTITUTE(X2;"A";""))<(LEN(SUBSTITUTE(X2;"G";"")));(LEN(SUBSTITUTE(X2;"G";""))<(LEN(SUBSTITUTE(X2;"O";"")));"AAG";
IF(AND((LEN(SUBSTITUTE(X2;"A";""))>(LEN(SUBSTITUTE(X2;"G";"")));(LEN(SUBSTITUTE(X2;"G";""))>(LEN(SUBSTITUTE(X2;"O";"")));"OOG";
IF(AND((LEN(SUBSTITUTE(X2;"O";""))<(LEN(SUBSTITUTE(X2;"A";"")));(LEN(SUBSTITUTE(X2;"A";""))<(LEN(SUBSTITUTE(X2;"G";"")));"AOO"; "AGG"

with X2 being the cell number of the unreduced calculation outcome and a few spaces put in for readability

and than add the gender digit and do a LOOKUP formula using a conversion table to convert the reduced code back into text

6 years 5 months ago #80947 by TMSWhite
TMSWhite replied the topic: how do I integrate this calculation?
Most of the equations you show are not valid mathematical equations, so it isn't clear what you are trying to do. Similarly, although the Excel formula looks valid, it isn't clear what it is supposed to do. Please describe what you are trying to accomplish.

Are you trying to control which options appear in question 6 and 7 based upon how the prior questions were answered (e.g. filtering question 6 and 7 to show a subset of the possible answers)?

6 years 5 months ago #80966
obiwancupcoffee replied the topic: how do I integrate this calculation?

Since what I need is based on three basic elements (Apple, Orange and Grape), that do not have a regular 1, 2, 3, 4, 5, 6, 7, 8, 9, etc. order, it's nearly impossible to use regular mathematical calculations (as far as I know).

You could however see them as a circular system, much like a clock with Apple at 0h00/12h00, Orange at 4h00 and Grape at 8h00, since you can order them 1, 2, 3, 1, 2, 3, 1, 2, 3, etc.
And there is a very complex way to use a kind of mathematical equation, in which the combination of the answer to two subsequent questions is based on the mean of the first answer and the last answer, by taking the answer to the first question as the starting point and the answer to the second question as a arc-vector with a direction (either clockwise or counter-clockwise) and a weight (how forceful is the push away from the starting point). But as I said before, that's not exactly using regular mathematical equations either.

I therefore chose to use logical equations, much like booleans, and some conversion of words into strings of digits at the beginning and back at the end.

I've tried to explain that in my previous post, but judging from your answer I didn't do a good job at that. Can you please give me an example of a way to explain it to you? (Or maybe it's a specific part of my post that wasn't clear? If so, can you please quote it?)

Which one of 18 possible versions of question 6 is going to be presented is based on a logical equation of the answers to questions 2, 3, 4 and 5, and an extra twist based on the answer to question 1 (the gender).

Of question 7 there is only one version, that is either presented or not, based on the answer to question 6. So that is a simple boolean.

The Excel formula that I included in my previous post is a way to condense the 13-digit outcome of the logical calculation (e.g. AAAOOOOOOGGGG or AAOGGGGGGGGGG) into a more useful 3-digit code (respectively OOG or GGG) that can easily be pinpointed within the constellation of the three basic elements (Apple, Orange and Grape).

I'm looking forward to hearing from you.
Thanks!

6 years 5 months ago #80967 by TMSWhite
TMSWhite replied the topic: how do I integrate this calculation?

Basically, if you can convert your logic into a mathematical equation, you can use it to control which questions are visible. Your expressions can use any of the supported functions . So, if you want to use polar math, adding or subtracting to the arc vector, you can. You can either use assessment mode to assign vector values to each answer, or just use those vector values instead of A, O, and G for each question. That way you can add the vector values to get the final score without needing to do lookups. Then, you can control question visibility by setting relevance to a range (like only show question X if the score is between 0 and 30).

Bottom line, if you can convert your logic to a mathematical equation that doesn't use code look-ups, you can implement it in Expression Manager.

5 years 1 month ago #100718
obiwancupcoffee replied the topic: how do I integrate this calculation?
Hi TMSWhite,

It took me an entire year to come up with the properly working mathematical equation, but now I finally have it.

I just upgraded to LimeSurvey version 2.00+ and found that I still don't know how to properly use this mathematical equation in the Expression Manager, since I'm using mostly Ranking-type questions/answers, and those can not be easily converted into a value using the assessment Mode...

I did however find that I might be able to work with the "Set conditions For This Question"-option to solve my problem, if only I could add the Boolean "OR" and "NOT" operators to combine conditions in stead of just "AND"...

Can you please help me to get either the Expression Manager or the Condition Setting to work in my case ?

Thanks - I'm looking forward to hearing from you.

( I have the mathematical equation available as it works in Excel, if that's of any help... )

5 years 1 month ago #100736 by tpartner
tpartner replied the topic: how do I integrate this calculation?

Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

5 years 4 weeks ago #100804
obiwancupcoffee replied the topic: how do I integrate this calculation?
thanks for the tip TPartner

using ORed conditions is going to be complex in this case, especially if I can only use "AND" and "OR" and not "NOT" and "ELSE"...

especially since I have :
2 possible answers for the 1st question (gender)
6 possible answers for the 2nd question (ranking)
6 possible answers for the 3rd question (ranking)
6 possible answers for the 4th question (ranking)
6 possible answers for the 5th question (multiple choice)
6 possible answers for the 6th question (ranking)

that would mean 2*6*6*6*6*6 = 15.552 individual scenario's to manually sort out...

...isn't it ?

I'm afraid that's a little too complex...

isn't there any way to use the Excel-formula ?

5 years 4 weeks ago #100835
obiwancupcoffee replied the topic: how do I integrate this calculation?
Hi TPartner and TMSWhite,

I've been thinking about a solution, since I read that it would be possible to hide a 'question'...
I would like to use such hidden questions to convert the entered ranking-answer to a question into a number-value that can be used as input for the Excel-equation...

the conversion I would like to use in the hidden question is like this :

values to be ranked (in the 2nd, 3rd, 4th & 6th question) : A, O, G

6 possible outcomes (to these 4 questions) :
AOG => 2
AGO => 4
GAO => 6
OAG => 8
OGA => 10
GOA => 12

and when all initial questions (the initial 6 questions, including these 4, in this case) have been answered, another hidden question can be used to calculate the outcome of the 'circular math mean' answer (of the answer to question 2, 3, 4, 5 & 6)

and the answer generated in that hidden question, combined with the answer to the 1st (gender) question, can be used create the (boolean) logic for the next 3 questions to be displayed

so I'm thinking of using 'layer' of 2 automated and hidden steps (= 'questions') to do this...

but I can't find the paragraph of the manual that outlines how to create these hidden conversion/calculation steps...

can you point me towards the solution please ?