Skip to main content

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 
Search instead for 
Did you mean: 
bobnewbie
Contributor II
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