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.
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.
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.
Dimension 2 is always sorted in the same order, for each value of the dimension 1, e.g.:
USA A
D
F
Canada A
D
F
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.
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?
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.
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.
