topic Re: Sort order, cyclic group not working in QlikView

Sort order, cyclic group not working

juleshartley — Wed, 13 May 2015 09:54:18 GMT

Hi All,

I have a pivot table with 2 cyclic groups as the first 2 columns.

The group 'ML Hierarchy' has a sort expression set in the group that = 'ML Hierarchy Sort'. I have shown the values in that field below for clarity.

However, the pivot table seems to be ignoring this sort order!

Anyone have any ideas?

Thanks,

Julian

Re: Sort order, cyclic group not working

juleshartley — Wed, 13 May 2015 10:18:12 GMT

Anyone?

Re: Sort order, cyclic group not working

marcus_sommer — Wed, 13 May 2015 10:43:11 GMT

I think the reason is that you only used a field without an aggregation which led to NULL and therefore qv ignored these sorting. Try instead avg([ML Hierarchy Sort]) as expression. Another possibility could be to include the sorting within the field(s) by using dual-fields: How to use- Dual().

- Marcus

Re: Sort order, cyclic group not working

juleshartley — Wed, 13 May 2015 11:39:02 GMT

Hi Marcus,

yeah, I tried using max() and avg() but made no difference. Turning it into a dual did work but causes other problems as there are sorts with the same numerical value, which seems to confuse Qlik. I'll have another go at this approach...

Thanks.

Re: Sort order, cyclic group not working

marcus_sommer — Wed, 13 May 2015 12:37:46 GMT

Sorting from pivots are quite difficult and often only the dual-approach worked. Instead of the aggregation expression you could try a lookup expression like:

pick(match([ML Hierachy], 'INB', 'Force ...', ...), 1, 2, 3, ....)

- Marcus

Re: Sort order, cyclic group not working

juleshartley — Wed, 13 May 2015 13:50:49 GMT

the sort now seems to work ok if I force it via a 'dual'. Thanks Marcus.

Only odd thing left now is that I have some functionality on the text format of the second dimension that says 'if the value is <x then make it italic'.

this works fine until I put the date field as a column heading. ie. it works fine if data is displayed vertically but not horizontally! Seems a bit like a bug to me...

Re: Sort order, cyclic group not working

juleshartley — Wed, 13 May 2015 13:51:48 GMT

Update: it also works if I switch the date dimension to a normal field, rather than cyclic group...

Re: Sort order, cyclic group not working

buzzy996 — Wed, 13 May 2015 13:56:54 GMT

can u check the check box, SortBy---> State--ascending and see?

Re: Sort order, cyclic group not working

Anonymous — Wed, 13 May 2015 13:59:41 GMT

Can you upload your QVW to take a look?

Re: Sort order, cyclic group not working

Gysbert_Wassenaar — Wed, 13 May 2015 14:16:04 GMT

One problem could be that a ML Hierarchy Sort value is not unique, but can occur for several values of the other dimensions. The sort order defined in the hierarchy is not defined in the context of the pivot table, but only outside chart contexts. To be honest pivot tables are bothersome beast with regards to sorting. Can you post a qlikview document that demonstrates the problem?