Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ljdlumley
New Contributor III

Sorting on Y value

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

1 Solution

Accepted Solutions
MVP
MVP

Sorting on Y value

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.

6 Replies
MVP
MVP

Sorting on Y value

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
New Contributor III

Sorting on Y value

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

MVP
MVP

Sorting on Y value

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
New Contributor III

Sorting on Y value

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.

MVP
MVP

Sorting on Y value

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.

MVP & Luminary
MVP & Luminary

Sorting on Y value


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