topic Re: Sorting in Pivot not working in QlikView

Sorting in Pivot not working

nikhilgarg — Sat, 06 Jun 2015 12:23:13 GMT

Hey,

I am trying to sort the data in my Pivot table based on expressions but it is not appling to Desk = 'Others'.

I ahve attached the excel sheet and .qvw . Please let me know about the solution.

Thanks

Re: Sorting in Pivot not working

Anonymous — Sat, 06 Jun 2015 12:53:10 GMT

Not a solution, but explanation:

The value of the sort expression for Desk is evaluated over the whole selected data set (just select any R1 to see), not per value of the first dimension (R1). It is impossible to have different sorting of dimension 2 for the different dimension 1 values.

Solution - I think I had something similar, cant recall the details at this time, but I certainly used a straight table and likely a data island to resolve it.

Re: Sorting in Pivot not working

nikhilgarg — Sat, 06 Jun 2015 13:16:48 GMT

Hey,

Thanx michael. When we select the Region it is working fine. Then why not when we donot select any region?

I couldn't understand your statement :

The value of the sort expression for Desk is evaluated over the whole selected data set not per value of the first dimension (R1). It is impossible to have different sorting of dimension 2 for the different dimension 1 values.

can you plz explain ?

Thanks

Re: Sorting in Pivot not working

Anonymous — Sat, 06 Jun 2015 13:29:09 GMT

Yes Nikhil.

Dimension 2 is always sorted in the same order, for each value of the dimension 1, e.g.:

USA A

Canada A

It simply cannot be ADF for USA but DAF for Canada - pivot table simply doesn't work this way.

The order of the second dimension is defined by your expression correctly, and the is evaluated over all the selected data. It behaves the way as if the Dimension 1 doesn't exist. To test - remove dimension R1 from you table, and see how it is sorted. The same exact way it will be sorted in your table with R1 for each R1 value. This is how it works.

Regards,

Michael

Re: Sorting in Pivot not working

nikhilgarg — Sat, 06 Jun 2015 14:34:30 GMT

Hey,

Thanks Michael. But i have attached my app and excel sheet in this reply. I would like to ask few qstns when Login is selected as Sorting filter :

1: The Order for D1 for very first value of R1 is B,D,A. It means for every upcoming R1, order for D1 should be B,D,A. Then why for USA , the order is broken. It's order is: F,B,Z,D,A. Why F at top a nd Z in between B and D ?

2: Also for Others, Sorting is not correct. It is 25, 21, 21 and 25. Why is it so?

can you plz explain.

Thankx

Re: Sorting in Pivot not working

Anonymous — Sat, 06 Jun 2015 16:02:35 GMT

Not exactly...

You assumption that sort is defined by the "very first value of R1" is nor correct.

I'm attaching your application with an additional table which has only one dimension Desk. It shows what exactly sorting order for this calculated dimension will be. It does change if you make selections or change sorting in the filter above of course.

So, the order is F, X, B, Z, D, A, Others. In the pivot table, some of the Desk values don't exist for some of R1, values. For example, R1 Others has only three Desk values - and they are sorted in the order you see in the "Sorting Rules" table. Take a careful look, you see that the order is the same, just some values are missing.

Re: Sorting in Pivot not working

antoniotiman — Sat, 06 Jun 2015 19:03:14 GMT

Hi Nikhil,

The Michael' s answer is correct.

However, an idea could be (is only an idea)

in script

Load ....

If(D1='Unassigned' or IsNull(D1),'Others'&' '&R1,D1) as D1,

...

from ...;

And in Dimension -> D1

So, You solve this issue, but it might not work for another.

Regards,

Antonio

Re: Sorting in Pivot not working

nikhilgarg — Mon, 08 Jun 2015 10:27:50 GMT

Hello,

I am trying to sort on baisi of Login using aggr function but it is giving me some absurd order in my attached app.

Can anyone help me on this ?

Thanks

Re: Sorting in Pivot not working

Anonymous — Mon, 08 Jun 2015 14:06:27 GMT

The aggr() returns an array of values, not one value. Try to add sum():

sum(aggr(sum(login), R1, D1))

It doesn't resolve your original problem, but at least the result is predictable - in your case, by Login, descending. Follows the same pivot table rules as I described above.

Re: Sorting in Pivot not working

nikhilgarg — Mon, 08 Jun 2015 15:21:05 GMT

Hey,

Thanks Michael, but i don't understand by your statement:

The aggr() returns an array of values, not one value".

Can you plz explain this with example ??

Thanks

Re: Sorting in Pivot not working

Anonymous — Mon, 08 Jun 2015 15:35:29 GMT

Let me try...

Say, you have sales by Country by Year:

Canada 2014 2015

45 35

USA 145 235

The result of aggr(sum(Sales), Country) returns array:

80, 380

where 80 is for Canada and 380 for USA.

To use it, you need to aggregate it further, to get sum, or avg, or min, or max... For example:

sum(aggr(sum(Sales), Country)) returns 460

avg(aggr(sum(Sales), Country)) returns 230

max(aggr(sum(Sales), Country)) returns 380

...