Pivot table - sorting by expression and total of dimension

[wuensche]

Hello,

i want to sort in pivot table.

CalcDim1 and CalcDim2 are calculated Dimension.

Dim3 and Dim 4 are strings.

I want to sort like the following table:

1. Dim4 downsize with expression Measure 2 << it works great

2. Dim3 downsize with TOTALS of expression Measure 2 -  Dim4   <<< This does'nt work.  But I don't know why. I know, i have to use the expression of Measure 2 and maybe a aggr() ?  

Does anyone have an idea or know, if it is possible?

CalcDim1	CalcDim2	Dim3	Dim4	Measure 1	Measure 2	Measure3
1-ABC	1-mmm	Mueller	Total		16.954,0
1-ABC	1-mmm	Mueller	123		10.000,0
1-ABC	1-mmm	Mueller	1234		6.444,0
1-ABC	1-mmm	Mueller	134		411,0
1-ABC	1-mmm	Mueller	45678		54,0
1-ABC	1-mmm	Mueller	46748		45,0
1-ABC	2-bbb	Berg	Total		14.591,6
1-ABC	2-bbb	Berg	867		12.974,0
1-ABC	2-bbb	Berg	345345		1.444,0
1-ABC	2-bbb	Berg	45674574		101,9
1-ABC	2-bbb	Berg	534534		42,0
1-ABC	2-bbb	Berg	42342		29,7

kind regars

[marcus_sommer]

This should be helpful:

Perfectly Sorting Pivot Table (by A-Z. y-Value set... - Page 2 - Qlik Community - 1483393

- Marcus

[wuensche]

Thanks for your answer. I found it already in the forum, but if try something like that it 'doesn't work. i don't know wrat is wrong.

[marcus_sommer]

I think it could become quite tricky especially if there are multiple sorting intended. Personally I would split the task and checking the sorting separately by a more/less reduced number of dimensions/expressions to ensure that each part alone worked like expected. After that combining the single parts step by step to find those combinations which seems to cause the issue.

Beside this using calculated dimensions may make things much more complex because their logic would need to be included within the sorting. Further it might be necessary to apply already a dynamic sorting within the aggr() itself and/or to use dual() as calculated dimension whereby the numeric part contains the wanted sorting.

Another potentially cause is the use of rowno() within your expressions because it disabled the sorting.

- Marcus