topic Re: Sorting pivot table by unvisible fiels in QlikView

Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 10:28:04 GMT

Hello,

In my DB I have this table:

CategoryID, SortOrder

A 5

B 2

C 3

D 4

E 1

I'd like to show ONLY the categories (joined with other data), in a pivot table SORTED by SortOrder;

Category Month, Hours

E Jan 5

E Oct 8

B Mar 10

B Nov 6

C Sep 9

C Dec 15

D Aug 5

D Oct 4

A Jun 6

A Aug 3

It seems that sorting by a field not shown in the dimension it's not possible in a pivot table; is there a trick to workaround this limit?

Thank you.

Re: Sorting pivot table by unvisible fiels

antoniotiman — Mon, 30 Oct 2017 10:44:08 GMT

Re: Sorting pivot table by unvisible fiels

avinashelite — Mon, 30 Oct 2017 11:10:27 GMT

Pull both the tables from DB , rename your CategoryID to Category ...then in the Pivot table got to sort Tab and select expression and add SortOrder

Re: Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 11:11:37 GMT

Hi Antonio,

Can you have a look at my attachment and find what's wrong?

Grazie 1000 😉

Nicola.

Re: Sorting pivot table by unvisible fiels

avinashelite — Mon, 30 Oct 2017 11:16:14 GMT

have you sent the complete image ?? i could see only sort

expression

Re: Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 11:21:15 GMT

I sent only the sort image to show that, despite of the descending sort-by-expression in the first column (status) choice, the pivot tables shows the data in another sort (and i'm not able to understand what is).

Re: Sorting pivot table by unvisible fiels

avinashelite — Mon, 30 Oct 2017 11:34:54 GMT

Give the expression based sort order for all the dimension columns and un-check other sort options ...I feel that is the issue

Re: Sorting pivot table by unvisible fiels

antoniotiman — Mon, 30 Oct 2017 11:42:53 GMT

Try

Max(Ordinamento)

Re: Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 14:58:14 GMT

QV File...

Thank you.

Re: Sorting pivot table by unvisible fiels

antoniotiman — Mon, 30 Oct 2017 15:17:50 GMT

Like this

Re: Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 15:29:38 GMT

...to tell you the truth it should be:

93, 91, 82, 81, 73, 72, 71

However, how did you achieve it? Can you attach the qwv file, pls?

Thks

Re: Sorting pivot table by unvisible fiels

antoniotiman — Mon, 30 Oct 2017 16:24:52 GMT

See Attachment.

Re: Sorting pivot table by unvisible fiels

Anonymous — Mon, 30 Oct 2017 16:34:42 GMT

Excellent!

I don't understand why it is necessary to specify Min(Ordinamento) and not only Ordinamento, but it's not important....

Thank you to everybody for the help and you, Antonio, for the solution.

Re: Sorting pivot table by unvisible fiels

antoniotiman — Mon, 30 Oct 2017 16:46:09 GMT

It Sorts Stato according to Min value of Ordinamento (aggr by Stato) :

91,81,71 (Desc)

Regards,

Antonio