2 Replies Latest reply: Jul 25, 2016 7:51 AM by Prashanth Reddy RSS

    10 top values and sorting in pivot table

    Aleksandra Siwierska

      Hi, I've got a few problems with pivot table:

      1) I would like to take just only 10 the biggest values from the "human" field, basing on field "value" which is the amount of sales. I just need the fields which are classified as 'sheep' (because others are: admin etc.). When I've took, in the expression, "<=10" it showed mi just only 8 values. Any help? Is it correctly written?

       

      if(Aggr(rank(sum(value)), human)<=12,

      if(password='sheep', human))

       

      And the second problem is to take, in the same way, 10 the smallest values.

       

      2) Second problem is with sorting that, I've got 2 dimensions and first is sorted correctly, but when I expand the list, there is no changes. Here the expression : if(sum(value), sum(value)). I would like to sort them by the amount of the value.

        • Re: 10 top values and sorting in pivot table
          Prashanth Reddy

          Hi ,

          1.For the first problem, will be having direct option to show both top and bottom values. Just click on Dimensions->Limitation

           

          2.For the second problem, for the first dimension it will be correct and for the second dimension the sorting will be for the individual key fields of the first dimension.

           

          Thank you

          • Re: 10 top values and sorting in pivot table
            Ruben Marin

            Hi Aleksandra,

             

            For the first one it can happen than two of the top in sales has a password<>'sheep', so they accomplishes the first 'If' but returns false on the 2nd', can you try with:

            if(Aggr(rank(sum({<password={'sheep'}>} value)), human)<=12, human)


            For the 2nd note that is sorted by dimension, so it first sorts the first dimension, if you expand the 2nd dimension, the sort of the first dimension is kept, ie:


             

            Dim1Dim2Expr1 (SUM)
            AZ4
            AY1
            BX3

             

            It's sorted correctly, 'A' its higher than 'B', then it's sorted by the 2nd dimension, if you want to mix values of the first dimension (Showing 'A', 'B', 'A') you may change to straight table.