Sort order, cyclic group not working

[juleshartley]

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

[juleshartley]

Anyone?

[marcus_sommer]

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

[juleshartley]

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.

[marcus_sommer]

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

[juleshartley]

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...

[juleshartley]

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

[buzzy996]

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

[Anonymous]

Can you upload your QVW to take a look?

[Gysbert_Wassenaar]

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?