topic Pivot Table - Sorting in QlikView

Pivot Table - Sorting

gfisch13 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I'm hoping there is an easy way to implement sorting on this pivot table. Basically, I'd like each expense type column to be sorted at the users will. So using Lodging as an example I'd like the user to click something and the data would be sorted by descending transaction amount for the Lodging field. Any help is appreciated. I've seen some rather complicated entries on this topic on this board, and since I'm a novice was hoping there is an easier way?? Thanks!

Re: Pivot Table - Sorting

sunny_talwar — Thu, 22 Sep 2016 12:14:18 GMT

Is it possible to share a sample with what the expectation is?

Re: Pivot Table - Sorting

gfisch13 — Thu, 22 Sep 2016 12:21:24 GMT

Here is a simple snapshot that I've done in Excel. Notice that the Lodging column is sorted in descending order. I'd like to have the user be able to select which column they would like sorted. Hope this helped. I cannot share the live data.

Re: Pivot Table - Sorting

sunny_talwar — Thu, 22 Sep 2016 12:25:38 GMT

Can you share the above data in the Excel format atleast

Re: Pivot Table - Sorting

gfisch13 — Thu, 22 Sep 2016 12:29:26 GMT

Sure - here you go.........

Re: Pivot Table - Sorting

sunny_talwar — Thu, 22 Sep 2016 12:43:20 GMT

Can you check if attached is what you want

Script:

Table:

CrossTable(Type, Data, 2)

LOAD AcctNum,

Name,

[Car Rental],

Lodging,

Airline

FROM

[PivotExample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sort:

LOAD * Inline [

Sort

Car Rental

Lodging

Airline

];

Here Sort field can be used to decide which field you want to sort by.

By Lodging

By Airline

By Car Rental

Used a sort expression

Sum({<Type =p(Sort)>} Data)

Re: Pivot Table - Sorting

gfisch13 — Thu, 22 Sep 2016 12:48:45 GMT

Perfect...........I feel like I'll never learn the proper syntax for these expressions. Thanks for all your help!!!!

Re: Pivot Table - Sorting

sunny_talwar — Thu, 22 Sep 2016 12:50:21 GMT

No problem at all, I am glad to help

Re: Pivot Table - Sorting

Anonymous — Thu, 22 Sep 2016 13:51:05 GMT

George , maybe this help you...

Set Analysis Wizard for QlikView | qlikblog.at

Andrea.

Re: Pivot Table - Sorting

gfisch13 — Thu, 22 Sep 2016 14:04:43 GMT

Andrea - wow - that will simplify things!! Thank you!!!

Re: Pivot Table - Sorting

Anonymous — Thu, 22 Sep 2016 14:12:35 GMT

George glad to help you

Attach also a manual of set-analysis

Andrea

Re: Pivot Table - Sorting

gfisch13 — Thu, 22 Sep 2016 14:16:39 GMT

Thank you!

Re: Pivot Table - Sorting

sunny_talwar — Thu, 22 Sep 2016 14:23:37 GMT

Here are some other resources:

A Primer on Set Analysis

Set Analysis: syntaxes, examples