Hi Could you please share workbook here so that we can help you in right way
I think they are alphanumerically sorted, e.g.
IF [Question Set ID] = "1" THEN
WHEN 1 THEN "1. Started Here"
WHEN 2 THEN "2. Started Elsewhere"
ELSE "3. Unknown" END
//should evaluate 'Started Here' before 'Started Elsewhere' even though E comes before H
ELSEIF [Question Set ID] = "2" THEN
WHEN 1 THEN "1. Less than full-time"
WHEN 2 THEN "2. Full-time"
//should evaluate 'Less than full-time' before 'Full-time' even though F comes before L
I think you're right. Great work around but I'm wondering if there is a way to accomplish the same thing without adding numerical values to the responses? Or, somehow use the number for sorting purposes and then display a different value (i.e. alias). I would prefer not to include the numbers if at all possible.
Thank you! Kate
Sadly, nope. The problem with that approach is that it gives me every possible value from all questions (there are nearly 40 in all) so even if I tried to manually sort that list I don't think it would work. I was hoping I could use the numbering tactic and then create a new alias -- something like (even though I know AS won't work):
ELSEIF [Question Set ID] = "5" THEN
WHEN 1 THEN "1. Very Little" AS "Very Little"
WHEN 2 THEN "2. Some" AS "Some"
WHEN 3 THEN "3. Quite a Bit" AS "Quite a Bit"
WHEN 4 THEN "4. Very Much" AS "Very Much
ELSE "Unknown" END
But I don't know the programming side of it well enough [yet] to figure out how to do that [grin].
P.S. If I end up using the numerical approach, I'll circle back and tag it as the Correct Answer. Cheers.
I can think of two options. Check out this simple example:
You can put the column to sort by as the first field on the row or column. This will force a sort by this column.
And then you can hide the first column by un-checking "Show Header":
Or better yet, use ID to sort Description directly: Use the dropdown arrow on the Description pill. Go to Sort.
Sort by "Field" and choose ID for the Field Name. This has to be aggregated in some way; I would just put max or min. Because there is a one to one relationship between ID and Description (as I believe there will be for your sort order and field), Max or Min will give the correct result.
And you can see it is sorted.
I hope this helps.
Also, it kind of looks like you could use your Response field to sort by - it seems to be numbered correctly. But if not, You could calculated a Sort Field by copying your IF statement above, and instead of response strings, put the sort number you want associated with that Response.
Thank you so much for this suggestion! Creating a new calculated field used specifically for sorting did the trick Joy.