topic Re: Sorting in Pivot Table in QlikView

Sorting in Pivot Table

Eric_Tay — Tue, 12 Aug 2014 07:25:07 GMT

Dear all,

Notice that when we sort using expression in Pivot Table, sometime the sorting is incorrect.

In below example, I would like to sort the Year, Division and Category dimensions using Sum(LineSalesAmount). When I expand the Division column, notice that the Sum(LineSalesAmount) for Year 2012 are not sorted properly, as shown below:

When I expand the Division and Category columns, notice that the Sum(LineSalesAmount) are not sorted properly, as shown below:

Below is my sorting tab:

Any tips how to sort the Year, Division and Category dimensions using sum(LineSalesAmount) correctly?

Regards,

Eric

Re: Sorting in Pivot Table

amit_saini — Tue, 12 Aug 2014 07:26:46 GMT

Eric,

Could you please share your application.

Thanks,

Re: Sorting in Pivot Table

Miguel_Angel_Baeyens — Tue, 12 Aug 2014 07:26:47 GMT

Hi,

Are you using the same expression to sort all three dimensions?

Miguel

Re: Sorting in Pivot Table

rubenmarin — Tue, 12 Aug 2014 10:02:30 GMT

Hi Eric,

I don't have a complete answer, I can tell you this is done because QV assigns the order to the field CategoryName before he draws the pivot table, so segment dimensions (Year and Division) are not taking into account.

Sportswear is the 2nd. in Europe because the value 'Sportswear' of the field CategoryName is the 2nd with more sales on current selections (not in the year-Division in pivot table)

If you define the 3rd dimension as =Aggr(Year & '-' & Division & '-' & CategoryName, Year, Division, CategoryName) you can see then is sorted correctly, but this cause your selections in CategoryName not working as the user probably wants (not to mention the bad-looking for the CategoryName)

In script you can use Dual(CategoryName, AutoNumber(Year & '-' & Division & '-' & CategoryName)) as CategoryName but weird things also can happen with selections.

Hope this helps you to find a solution.

Re: Sorting in Pivot Table

Eric_Tay — Wed, 13 Aug 2014 10:13:31 GMT

Hi Miguel,

Yes, I am using the same expression to sort all three dimensions.

Eric

Re: Re: Sorting in Pivot Table

Eric_Tay — Wed, 13 Aug 2014 10:20:03 GMT

Hi Amit,

Here is the file:

Thanks,

Eric

Re: Re: Sorting in Pivot Table

Eric_Tay — Wed, 13 Aug 2014 10:21:18 GMT

Thanks Ruben,

Let me try it and I will let you know whether it works or not.

Eric