Ranking Issue within Pivot Table Expression

bobnewbie — Fri, 08 Jun 2018 13:54:58 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

topic Ranking Issue within Pivot Table Expression in QlikView

Ranking Issue within Pivot Table Expression