1 Reply Latest reply: Oct 14, 2016 2:33 AM by Vineeth Pujari RSS

    Pivot table to show top 20 rows

    Malaika S

      Hey All,


      I am trying to getting top 20 rows in pivot table but it is not working for me. Just a brief background on the requirement :


      1. Columns to have Traders and Rows to have CP hence using Traders and CP as dimension.

      2. Color coding based on 20%, 30% and 50%

      3 Have list of 50 traders in the database but need to show only default 10 traders with a trader filter for adding more traders (incase required). So the dashboard will just show 10 traders by default


      Steps :

      1. Create a variable(vdefaulttraders) to show only 10 traders. Get selectedcount>0 Traders ='Trdr1',Trdr2'  & concat & Trader, 'Trdr1','Trdr2' so on...

      2. Dimension CP and Traders

      3. Expression - sum({<Trader={$(vDefaulttraders)}>}Value)


      Everything worked till the percentage coding but i am unable to show top 20 rows even after using aggr, rank in the expression. If i use aggr(rank(sum.....) in dimension it shows top 20 but the % percentage nos messes up becoz the denominatore is based on top 20 transactions instead of total of all transactions.


      Any help in this regard will be highly appreciates. I need to fix this at the earliest hence reply asap.




      PS: Cannot send the qvw file