topic Re: Pivot table expression sort option in QlikView

Pivot table expression sort option

Anonymous — Mon, 26 Mar 2018 20:37:27 GMT

HI All,

I have a requirement to sort the pivot table which has following demensions and measures:

Dimensions: aName

Region

ProdName

System Name

Measure: MeasureChange

Sort order requirement:

aName sort order should be

Alert3,

Alert2,

Alert1

Region sort order should be

RegionName1

RegionName2

RegionName3

RegionName4

RegionName5

RegionName6

Product Sort order :

ProductName2

ProductName1

System Name sort order should be on measure change column and it should be in descending order.

I tried to use the expression sort order using following expression on system name :

=dual(SystemName,aggr(sum(MeasureChange),AlertName,RegionName,ProductName,SystemName))

still I am not able to achieve above mentioned requirement. Please suggest. Mocked app has been attached. !

Re: Pivot table expression sort option

Anonymous — Tue, 27 Mar 2018 01:11:34 GMT

Try this...

Re: Pivot table expression sort option

vinieme12 — Tue, 27 Mar 2018 02:05:37 GMT

You can use either Match() or Wildmatch() for custom sorting

Another Option is to use Dual() to assign numeric values to the column values during load

example for Dual

Load

Dual(AlertName,Match('Alert3','Alert2','Alert1')) as AlertName

Re: Pivot table expression sort option

Anonymous — Tue, 27 Mar 2018 02:12:42 GMT

Hi Vineeth,

Still it has been broken on following criteria:

Alert1

RegionName1

Product Name1

System Name1 and 19 should be coming other way as per the sort logic.

Re: Pivot table expression sort option

vinieme12 — Tue, 27 Mar 2018 03:58:35 GMT

If you want SystemName19 to be on Top then ProductName will be sorted in Ascending Order!

Can you confirm what exactly you need because you specified Descending Order for ProductName!

Re: Pivot table expression sort option

Anonymous — Tue, 27 Mar 2018 14:48:14 GMT

System name19 should come in the top of the ProductName1 subsection because it has got measure value of 12 and System Name1 should come below the SysstemName19 because it has got measure value of 10.

Re: Pivot table expression sort option

Anonymous — Tue, 27 Mar 2018 15:40:51 GMT

System name should be sorted on the basis of measure descending order