Sorting a pivot table

[ramonarcusa36]

Hi guys.

I'm facing a problem trying to sort a pivot table properly. I'm attaching the QVW document which can be used as an example of what I'm trying to do.

I think the idea behind all this is simple. I have three dimensions: Organization, Section and Month Number. My table shows one column per Month (with the total of the year at the beginning), and one row for each Organization - Section combination. There's only one expression in the table: Sum(Sales).

I need my table to be properly sorted, that is: Organizations have to be sorted in descending order (no problem with this), and Sections have to appear properly sorted as well within each Organization (here we've got the problem!).

As you will notice, the same Section can be found in several organizations, for example, GL belongs to both 100 and 2000 Organizations. When QVW tries to sort Sections, it seems like it's just looking at the overall value for each Section, without paying attention at the context in which it is. That makes GL to be the second Section for Organization 100, and that's not correct, because it should almost be at the bottom of that Organization with only 17 units of sales. The same thing is happening with Section GK: it just has 1 unit for Organization 100, but since it has 10898 units for Organization 500, it's being promoted to the sixth position in Organization 100. That has no sense for me. Every Section should be sorted within each Organization.

Is there any workaround to solve this? I've tried modifying the sorting expression with Aggr, SUM(TOTAL...) and other tricks, with no success.

Thanks for your help!

EDIT: I forgot to say that you can look at the right table to check how I'm expected the left table to be sorted. It seems like having the Month columns is driving QlikView crazy.

[rubenmarin]

Hi ramon, you can try changing Section dimension to a calculated dimension using dual(), so internally each dimension value has a different value:

=Dual(Section, FieldIndex('Organization',Organization)&FieldIndex('Section',Section))

or:

=Dual(Section, FieldIndex('Organization',Organization)*1000 + FieldIndex('Section',Section))

[ramonarcusa36]

Thanks Ruben.

I see that could do the trick with this simple example, but I'm afraid that won't work in my real document because Organization and Section must be Cyclic Groups. And I guess those Groups wouldn't behave as Groups if I use the Dual function. I don't see how both things could combine.

[rubenmarin]

You can still use the same trick, with some dynamism to use the active field in the group:

=Dual(GetCurrentField(CyclicGroup1Name), FieldIndex('$(=GetCurrentField(CyclicGroup1Name))', $(=GetCurrentField(CyclicGroup1Name))) & FieldIndex('$(=GetCurrentField(CyclicGroup2Name))', $(=GetCurrentField(CyclicGroup2Name))))

[ramonarcusa36]

But... if I create that calculated dimension, the dimension will not behave as a group, I mean, I don't get the orange round arrow icon on the header of the column. It wouldn't be a cyclic group anymore.

Otherwise, I don't know if you mean I should use one dimension for the cyclic group and a different one for the calculated dimension....

[rubenmarin · Accepted solution]

You're right, things of multitasking... it caused that I forgot details.

Maybe you can modify the 2nd cyclic group (or create another one if this one is used in another charts).

The fields of this 2nd group can be set as calculated dimensions.

=Dual(GetCurrentField(CyclicGroup1Name), FieldIndex('$(=GetCurrentField(CyclicGroup1Name))', $(=GetCurrentField(CyclicGroup1Name))) & FieldIndex('ThisGroupFieldName', $(=GetCurrentField(ThisGroupFieldName))))

Not tested.  If this doesn't works, can you update your sample to something similiar to your real scenario?

[ramonarcusa36]

Well, it seems I could get to sort my table with your approach. Every field in my Cyclic Group is now an Expression using the Dual function. And it seems to sort properly.

Though, there are two things I'm not sure if I like: 1) My cyclic group turned out to be complicated (not easy to understand and maintain in the future) and 2) When I make a selection in the second column, it also makes a selection on the first one. I know this is a normal behavior of QlikView because the second dimension also contains the first one (inside the Dual function), but still, it may be a bit confusing for my users.

Is there any other workaround not involving complicated cyclic groups? I mean, any trick on the sorting expression, with aggr or TOTAL or any other advanced syntax?

Thanks!

[rubenmarin]

Hi Ramón, I don't know any other way (wich doesn't means it doesn't exists), based on the test I did seems that the sort is applied to field values, and GH is the same value no matter wich organization has in the previous dimension.

If GF has sort '1'  and GL has sort '2' it will show GF before GL in all dimensions. To avoid this I try to make each dimension value different, so it can have a different sort order by each Organization (that's why it also filters organization).

Without the vertical dimension you can use sort by Y-Value but I don't really undestand what sorting is doing when there is a vertical dimension.

If you find a simpler way I will be glad to hear it, I'm always open to learn new techniques.

[ramonarcusa36]

Well, after working on this for some hours, I think I'll go with the Dual solution. I won't spend more time on trying to find a better way.

Thanks a lot Ruben!

[rubenmarin]

Glad to help! I wish some day someone finds an easier way to do this.