I'm designing a survey of business financial data and need it to look more or less like a spreadsheet to the participant. In other words there is a matrix of inputs for the participant, with columns for different years and rows for different revenue and expense items, etc. It would also include columns of formulas/expressions that re-calculate in real time to serve as kind of an error checking-- presumably participants will notice a number is out of whack when the percentage change with a prior period is very high or low, etc.

The survey would contain approximately 400 question inputs (mostly numeric) and 600+ equations/expressions. The total number of participants would be 400-500.

I have attached an example of a section (using some dummy question topics) to give you an idea of what it involves.

My question is multi-part:

1) I'm looking for ideas on how to tackle the presentation. It looks like an array of course, so an Array of Numbers question combined with equations/boilerplates is one option. But I can see it would require a lot of javascript tweaking to organize the questions in the browser. And as long as we are doing that we could just as well use numerics and multi-numerics and arrange them in the same manner. How likely is this to become unwieldy and buggy as we get into hundreds of questions?

2) Many of the inputs are non-mandatory. But we also want to have subtotal rows which are mandatory and which either let the participant enter a subtotal manually or, if they have entered the preceding rows, just sum up those entries. Since I don't believe you can make one subquestion mandatory and another optional (except with js), this is another reason why I'm thinking lots of individual numeric questions would be more flexible.

3) What is the perfomance going to be like with this many equations and expressions? Can the Expression Manager keep up?

4) How will we fare with over 1000 columns in the survey_nnn table? We would use MyISAM storage, and theoretically it will be ok, but I'm wondering about real-world experience.

5) Would we be better off just collecting data with a different tool? It's really not feasible in the time frame, but we could produce a better spreadsheet-like interface with jqgrid or another library. Or we could simply collect the data in 100s of Excel workbooks and consolidate (sounds fun!). Or Google app engine, google docs? Just thinking outside the box.

Sorry for the long question!

Here is the example

Fred wrote: I recommend to use the "array text" question type (link: docs.limesurvey.org/Question+type+-+Arra...ey#Short_description) with a "numbers only" setting for the inputs and "show grand totals" enabled for columns:
docs.limesurvey.org/Question+type+-+Arra...vey#Show_grand_total

As for the presentation: The only solution seems to be some JavaScript to get the exact same layout, but this is getting very complex for such a vast range of questions.

An alternative might be to store all the equations in single questions of type equation and then use a text display question type with a table layout to place all placeholders nicely into such a tabular layout to show all the calculated values.

Fred wrote: You can make certain sub-questions mandatory by using the "Question validation equation" and the "question validation tip". The tip can be used to show a hint like "The subquestion 'How much beer do you drink each year' has to be answered" and the validation sinply needs to point to one or more sub-questions, e.g. by using a !is_empty() check.

Fred wrote: That is hard to predict. Generally, Limesurvey 1.92 is a little faster when it comes to equations but there have also been some speed improvements recently. But it should be possible to simply check on both system.
Another issue is the survey mode. If you put all these questions on one page, you can imagine that there will be a huge loading time. So running the survey in question by question or group by group with only some very few questions in each group would be my recommendation.

Fred wrote: Again, this is hard to tell. Using lots of array questions will result in one column for each sub-question so you might run into some limitations.
Try activating the survey from time to time while designing it to check if you are getting problems here.
If so, the only solution is to split up the survey and pass some data from survey A into (hidden) questions of survey B if required.

Fred wrote: Do you want to re-invent the wheel or better try to make use off all of Limesurvey's capabilities and if necessary, extend some of these by some custom JavaScript?!

I think the biggest problem you might run into is the column limit of the DBMS. You can create a first draft of that survey, maybe just 5% of it and then use group export/import 20 times to get to the expected survey size to check if you can still activate the survey.
If the answer is "no" and there are lots of conditions which would have to be passed on from survey A to survey B when splitting it up, checking for other tools makes sense.

The general question is if other tools can easily do all the calculations at runtime AND present the forms in a more or less nice way.
Fred wrote: Platinum donators are allowed to ask even longer questions

Marcel,

Thanks for the amazingly thorough response. You have some great suggestions.

1. Array Text was exactly what I was looking for. I kept trying Array of Numbers (because of the name obviously) but couldn't find the totals feature. In reality, the automatic totals aren't going to work for all cases anyway because the participant can't edit them and they are not saved to the database so we will have to make our totals.

There's still no alternative to using a LOT of javascript to change the presentation. Either I have a lot of separate questions and then push them around the document to line up, or alternatively, maybe I can build a big array/texts matrix and control the inputs with javascript so that some of them behave like totals.

2. Will definitely try the subquestion validation. I didn't realize you could apply it to just ONE subquestion. This is EM syntax I assume, such as [!is_empty(pepsi) and !is_empty(coke)] or something.

3. I will definitely break it into group by group mode, let people save and resume, etc., since there are so many questions. There will still will be dozens of questions on a page. I'm debating whether to even try this with LS 2.0 since it's so new and there are still bugs. It's unlikely I can test it on both systems since the javascript and template changes don't easily port to both versions.

4. Splitting up the survey is great idea. Also there is a big section of demographics, about 25% of the questions in this survey, that I was proposing to make a lot shorter, but if they won't do that then I can carve into a second "participant profile" survey. I have been working on a participant "panel" interface that helps with the presentation and gets people in and out of the right surveys, but unfortunately it isn't quite ready to use in this case.

5. Great idea for cloning the groups to test. You're right I don't want to reinvent the wheel, although with enough time and budget I would do it anyway -- a square wheel maybe

Hello,

For this survey type, i use sometimes a group like that:

Put the HTML needed in the group description.After adding 4 question (A1,A2,B1,B2 ), and a script :show some way to move only answers/question etc ...

Denis

I have done something similar to Denis on several occasions, to mimic the look of a bank statement or a weekly planner.

However, instead of loading the HTML in a question or group description, I place it in a remote file and pull it in with AJAX (I find it way easier to edit this way) and then move the inputs into the appropriate cells.

Thanks Denis and Tony. I didn't think of setting up the table first. Now if Thomas White and Carsten and a couple more weigh in then we have the "brain trust" here

In the worst case, we will need to "hand code" every table, question, and function since it specific to the group. But it would be nice to have a more generic approach that speeds up development.

Many of the groups will have the same column structure, with a variable number of rows. For example, maybe I could encode the cell id in the question title (eg., question10-a1, question11-b2, etc.), and then have a more abstract function that parses out the cell ids, figures out the number of rows by looking at the questions and then builds the table and moves the elements in. Of course it could take longer to do this than to build the survey manually, but then I'd be able to use it again on a different survey (maybe).

There are lots of ways to skin a cat fortunately.

Fred wrote: Yes,

But when make some table like that : with HTML structured directly table, it's more easy to see the "displayed to public table" before adding the question. With your solution, the complexity it's not only in developement of the javascript but to t set the good code (and to understand what is the good code for an beginner admin).

It's an idea, maybe with some "What code go where" before working on javascript .

Denis
PS: tpartner: i've done this with a lss export + template with 1.87 version. Gloups

Yes, we are going to try as 1000 individual questions arranged into tables using jquery.

Many of the rows have the same calculations (e.g, Column C divided by Column D) so it is tempting to do the formulas in Javascript in the browser and then save them like regular inputs, as opposed to than using Equations, which we would have to build one at a time. To be determined.

Just some short comments late at night:

A
2. Will definitely try the subquestion validation. I didn't realize you could apply it to just ONE subquestion. This is EM syntax I assume, such as [!is_empty(pepsi) and !is_empty(coke)] or something.

-> Yes, that is simple EM syntax which emans that you can address each subquestion individually or even combine them.


B
Splitting up the survey is great idea. Also there is a big section of demographics, about 25% of the questions in this survey,
->If you are running into the column limit problem, this is definitely a good set of questions to be put into its own survey.
You then only need to pass the users data set ID of that survey into a hidden question at the next survey to be able to merge data later.


C
Many of the rows have the same calculations (e.g, Column C divided by Column D) so it is tempting to do the formulas in Javascript in the browser and then save them like regular inputs, as opposed to than using Equations, which we would have to build one at a time.
I'm not sure if JS is the best choice. Of course, having a single equation question for each value you want to store is also no good alternative, but there might be another approach:
Since you can use EM for default answers of text questions, you can create a multiple short text question with dozens of answer options and then simply use the default answer field to store an equation for certain calculations in there like {total/days} or {max - min}.
This way you don't need to take care of the calculation yourself and only have to assign question (and answer) cods carefully and you can even use the available EM math functions.

Later you can easily hide the question with one line of CSS.

Hope this helps...