topic Re: Sorting within a dimension in three dimension pivot table in QlikView

Sorting within a dimension in three dimension pivot table

Thu, 13 Mar 2014 06:26:58 GMT

Hi. I have what I though was a simple problem until I went down the rabbit hole!.

I have a table below (sample data):

What I need to do is sort by the first Qty value. Ie Qty=1. Therefore the order for SL should be: Zane John Peter, or Zane Peter John, and the sort for AL should be John Peter.

I though this would be simple by setting the sort for the person dimension to Sum(if(qty=1, qty))

Ie, only include values where the qty dimension is '1'

But it doesn't work.

Can anyone offer a suggestion.

Thanks in advance (sample QVW attached).

Re: Sorting within a dimension in three dimension pivot table

Thu, 13 Mar 2014 07:34:38 GMT

Hi,

you can use "Ord(expression)" in sort tab Expression,

Then you can get the correct order

Re: Sorting within a dimension in three dimension pivot table

Thu, 13 Mar 2014 07:44:01 GMT

In pivot table, there is no direct option for expression sort. In sort tab, for type and person dimension,add your Quantity expression in expression option and make it as descending.

Hope this will help you...!!!

Re: Sorting within a dimension in three dimension pivot table

Thu, 13 Mar 2014 21:20:26 GMT

Thank you for your prompt replies. But I don't think you understand the issue. I need to sort the person dimension based on only the contents of the Qty dimension for a value of 1.

If you are able to do this, can you please make changes to the sample file to demonstrate.

Thanks.

Re: Sorting within a dimension in three dimension pivot table

Josh_Good — Thu, 13 Mar 2014 21:40:48 GMT

Try sorting the person dimension using the expression:

=Sum({$<Qty = {1}>}Qty)

in Ascending order

Re: Sorting within a dimension in three dimension pivot table

Josh_Good — Thu, 13 Mar 2014 21:54:53 GMT

Thinking on this further. My solution only worked because of a quirk in your data. I don't believe it is possible to have a different sort order for a dimension nested inside another dimension. Zane keeps coming when sorting in descending order because he has the least qty for Qty = 1 overall.