Sorting on Y value

[ljdlumley]

Hi, this is probably a basic question but I can't find the solution on the forums.

I have a dimension 'Year' containing 2009 and 2010. When I create a bar chart (grouped rather than stacked) and sort on the y value the data is sorted by the combined y values of 2009 and 2010 i.e. department 1 has a score of 90 in 2009 and 90 in 2010, department 2 has a score of 70 in 2009 and 100 in 2010. Department 1 is first in the chart as the combined scores are 180 as opposed to department 2 score of 170. However, I would like to sort by the value for the latest year so that department 2 comes first in the chart.

Any thoughts on how I'd achieve this?

thanks

John

[johnw · Accepted solution]

Maybe sort by expression descending, and use an expression like this?

sum({<Year={'$(=max(Year))'}>} Score)

Or whatever your score expression is, plus set analysis to tell it to only pull in the latest year.

[ljdlumley]

Thanks John, that works exactly as I wanted.

Now I'd like to make it a bit more complicated! I've actually got 5 expressions that I have grouped together to cycle through (these are different categories of a survey) so whereas in the example above I would use my 'Score' expression, I'd like a way to include a variable in the formula to pull through which expression is currently selected.

cheers

John

[johnw]

Unfortunately, I don't think there's any way to detect which expression is currently selected. What I've done in similar situations is create a table with fields disconnected from my data model, one field per expression I want:

LOAD * INLINE [
Something, Something Else, Other
];

Then I put those fields in a cyclic dimension group. Then I use the selected dimension to drive which expression displays:

pick(match(getcurrentfield(MyGroup),'Something','Something Else','Other')
,sum(Score)
,avg(Score+Score2)
,max(Score2))

And your sort expression could be:

pick(match(getcurrentfield(MyGroup),'Something','Something Else','Other')
,sum(({<Year={'$(=max(Year))'}>} Score)
,avg(({<Year={'$(=max(Year))'}>} Score+Score2)
,max(({<Year={'$(=max(Year))'}>} Score2))

There might be a cleaner way, but I'm not thinking of one. At least it's A way, assuming it works, and if nobody comes up with something better.

[ljdlumley]

I'm afraid that has defeated me, I've been trying all day to get that to work on my data. I keep getting the error message 'bad field name' when i've typed in the name of the cyclic group I've created with the new fields.

I'll try again after the weekend.

[johnw]

Here's an example using a cyclic group to mimic an expression group, including a sort order that depends on which expression is currently selected. It's obviously not your specific case, but perhaps a simplistic example will still be helpful.

[rwunderlich]

ljdlumley wrote:I keep getting the error message 'bad field name' when i've typed in the name of the cyclic group

This is not a true error. It's just a limitation or bug of the syntax checker. Ignore the error message and press on.

-Rob