# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Contributor II

## Ranking Issue within Pivot Table Expression

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

0 Replies
Community Browser