Pivoting table sorting problem

[bobnewbie]

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

[sunny_talwar · Accepted solution]

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

[bobnewbie]

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.

[bobnewbie]

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]))

[sunny_talwar]

Super