Sorting in pivot table

[puneetagarwal]

Hi All,

I have requirement 

Sorting for Others
Requirement is to show the top 20 Entity and group the balance debtors under Others and display Others portion in the last row of the Pivot. We are using rank to create another category called Others. However we are unable to display Others in the last row

Please amend as required

Thanks in advance

Regards,
Puneet Agarwal

[sunny_talwar]

Would you be able to share a sample where we can see this issue and test it out?

[Yoshidaqlik]

Hi

You can use the same measurement formula to create the sort

=AVG(
  Aggr(
      if(
      	rank(sum(Value),4)<=20,sum(Value),-1
      )
  ,Dimension)
)

regards

[puneetagarwal]

Sunny,

Sharing a QVF may not be possible.. 

@Yoshidaqlik 

My calucated dimension looks someting like this : if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,Entity,'Others')

Can you please suggest/ provide so that i can short starting 19 entity in desc order and others at bottom.

TIA

Regards,

Puneet Agarwal

[Yoshidaqlik · Accepted solution]

In Classification Part of Object Select the "Sort by Expression" Flag

put the expression that passed just adapting the return

if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,sum({$<MONTH={"$(vCurrentMonth)"}>} Sales),-1)

[puneetagarwal]

Hi Yoshida,

Thanks a ton i got the solution. but unfortunately couldn't understand the expression,if you have time can you please elaborate the expression (explain).

Regards,

Puneet Agarwal

[puneetagarwal]

Hi Yoshida

I have posted one more query, can you please look into it. Below is the link

https://community.qlik.com/t5/New-to-Qlik-Sense/Measure-in-x-axis-and-dimension-as-y-axis/m-p/161642...

Regards
Puneet Agarwal

[Yoshidaqlik]

if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,sum({$<MONTH={"$(vCurrentMonth)"}>} Sales),-1)

clear

explaining about the function

the rank function will bring the sum positioning

Aggr function assembles a virtual table over the Entity dimension

it's like you have a table with an Entity column and a measurement column that would be the sum

After this table is assembled a test is made if the rank is less than or equal to 20, if the test is true it will bring the sum result to rank otherwise it will bring the number -1 leaving the Other position last

[puneetagarwal]

Hi Yoshida,

Thanks a ton for the clear explanation.

Regards,

Puneet Agarwal