Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bobnewbie
Contributor II
Contributor II

Pivoting table sorting problem

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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

bobnewbie
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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