topic Sorting on Y value in QlikView

Sorting on Y value

ljdlumley — Wed, 08 Sep 2010 17:12:24 GMT

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

Sorting on Y value

johnw — Wed, 08 Sep 2010 17:16:22 GMT

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.

Sorting on Y value

ljdlumley — Thu, 09 Sep 2010 10:48:15 GMT

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

Sorting on Y value

johnw — Thu, 09 Sep 2010 18:17:15 GMT

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.

Sorting on Y value

ljdlumley — Fri, 10 Sep 2010 16:57:17 GMT

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.

Sorting on Y value

johnw — Fri, 10 Sep 2010 17:22:05 GMT

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.

Sorting on Y value

rwunderlich — Fri, 10 Sep 2010 18:29:57 GMT


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