Sorting values in y-axis based on selections in cycle group in x-axis

[#1]

Hi all,

I have a chart where the x-axis uses values from a cycle table and I have to sort the y-axis values based on the selection from the cycle table in the x-axis. The y-axis values are derived from expressions and are numeric.

For example, if the cycle group has fields A,B in it and the y-axis has expression sum(C), then when I select A, the y values should be sorted in the descending order.

How can I go about this ?

[johnw]

Sort by expression, something like this for instance, to sort A descending and B ascending by the Y value:

=if(getcurrentfield(MyCycleGroup)='A',-sum(C),sum(C))

[#3]

This solution works quite nicely. It's a bit cumbersome if your expression is lengthy, and now you need to maintain the expression in two places, but it does work.

I especially like the -1 * (value), that reverses the sorting order. I wonder how to do that with text values?

[johnw]

You could do this to avoid one duplication:

=if(getcurrentfield(MyCycleGroup)='A',-1,1) * sum(C)

If sum(C) is an expression in the chart, you MIGHT be able to refer to it by column name or column number. If not, you could put the expression in a variable, and just use the variable as the expression and in the sort order.

I'm not sure how to easily reverse the sort order on text values. An ugly way would be to make two charts and use a display condition to show the chart with the desired sort order. Surely there's a simpler way.