topic Re: Pivoting table sorting problem in QlikView

Pivoting table sorting problem

bobnewbie — Thu, 14 Jun 2018 07:20:36 GMT

Hi All, please can you assist:

I have sales reps linked to more that one region, and I only want to show the sales rep along with their top selling region and figures.

I have created this dimension which is set to Suppress When Value is Null, which gives us a value of 1 per top selling sales rep/region combination. Else a null. This Null along with the Supress Null checkbox removes all the other regions per Sales rep that we don't want.

=IF(aggr(

rank(

aggr(

sum( {<CommPeriod={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} Actual),

[Planner Code],

[Reporting Region ShortName])

[Planner Code],

[Reporting Region ShortName]

)=1,

aggr(

rank(

aggr(

sum( {<CommPeriod={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} Actual),

[Planner Code],

[Reporting Region ShortName])

[Planner Code],

[Reporting Region ShortName]

NULL())

This works great along with the expression formula:

sum({$<[CommPeriod]={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} [Amount Remuneration PC])

The problem is only with sorting the pivot table, when I sort by the same expression, the figures are sorted according to Sales Reps total figures for all regions. Example a rep with a value of 2 and 4 for 2 regions is ordered above a rep with a value of 5. Although the expression value is correct:

CURRENT RESULT: ACTUAL DATA:

SALES REP TOTAL SALES REP TOTAL

JANE 4 TOM 5

TOM 5 JANE 4

JANE 2

Totals are correct, sorting incorrect. Anyone have any suggestions please

Re: Pivoting table sorting problem

sunny_talwar — Thu, 14 Jun 2018 11:10:57 GMT

Sorting a pivot table is always challenging, would you be able to share a sample to play around with it?

Re: Pivoting table sorting problem

bobnewbie — Fri, 15 Jun 2018 06:20:13 GMT

Thanks Sunny, I came right. I was trying to do the entire aggr/ranking functionality without using an if statement and purely through set analysis. After looking at plenty of samples, I went with the if statement route and it worked. If you create a dimension using aggr/ranking, and hiding on null creates other issues and purely doing it in the expression is the answer.

Re: Pivoting table sorting problem

bobnewbie — Fri, 15 Jun 2018 06:21:23 GMT

This was my final expression:

=Sum(Aggr(If(Rank(Sum({<[CommPeriod]={$(vMonthRun)},[Planner In Service Indicator]={Yes}>}[Amount Remuneration PC]))=1, Sum({<[CommPeriod]={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} [Amount Remuneration PC])), [Planner Client Number],[Reporting Region ShortName]))

Re: Pivoting table sorting problem

sunny_talwar — Fri, 15 Jun 2018 11:05:48 GMT

Super