Sort by Expression undocumented limitation found. Is it a bug?

[#1]

I've just figured out the root cause of a problem that has been irritating me for the past couple days.

The issue arises when you have two dependent dimensions (e.g. a Question dimension and an Answer dimension, whereby the answer dimension depends on the question dimension), and you are sorting the dependent dimension (e.g. the Answer the dimension) by an expression (e.g. an AnswerOrder field associated with the the Answer field). The problem arises when two questions share one or more of the same answer dimension value but a different AnswerOrder value. This could arise because one set of answers is on a scale of 0 to 10, and another on a scale of 1 to 10, but each sets are ordered from 1 going up.

So in the first set 0 would have order 1, 1 has order 2, 2 has order 3, and so on.

In the second set 1 would have order 1, 2 has order 2, 3 has order 3, and so on.

Therefore in the first set, 1 has order 2, but in the second set 1 has the order 1, which is different.

Unfortunately it does not seem possible to create an absolute sort order for the dimension value. Nor is it possible to specify a sort order across 2 dimensions.

Therefore, the fix would be to re-calibrate the sort orders for all answers so they are all the same.

That said, I see this as a workaround. Depending on the question, you may want to sort the answer values differently even though the answer values are the same.

Anyone else encountered this issue before?

Cheers,
Neil.

[johnw]

From your tags, I gather this is a pivot table issue. If so, and if I understand your issue, then yes, others have encountered the same problem. A fix has been requested a couple times on the customer portal, assuming you have access:

https://emea.salesforce.com/ideas/viewIdea.apexp?id=087200000008T02&srPos=1&srKp=087
https://emea.salesforce.com/ideas/viewIdea.apexp?id=08720000000HLlc&srPos=0&srKp=087

It also wouldn't surprise me if straight tables have the same limitation, though I haven't checked.

I don't consider it a bug. Many people probably do want the sort order of one dimension to not depend on the other dimensions. QlikView supports this requirement, so it's likely working as designed. However, it really should ALSO allow you to have the sort order depend on the other dimensions. I think that both options should be supported, with the current approach as the default, but that's a feature request instead of a bug. Thus the requests linked above.

I also haven't checked to see if there are any changes in version 10, as I'm still on version 9SR6.

[#3]

Thanks John - you've helped put my mind to ease.

Indeed I am working with a Pivot Table.

I've already commenced ETL development on Answer order re-calibration to avoid this issue.

Not on the partner portal yet - I'm waiting on my Partner Support exam to be marked.

Cheers,
Neil.