3 Replies Latest reply: Jun 6, 2018 4:58 AM by Sunny Talwar RSS

    Max percentage by row and column

    nicolas sinquin

      Hy,

      I've data like this

       

      New_Table:

      LOAD * Inline [

      Dim1, Dim2, Value

      D1_1,D2_1,1

      D1_1,D2_1,1

      D1_1,D2_1,2

      D1_1,D2_1,5

      D1_1,D2_1,5

      D1_1,D2_1,1

       

      D1_2,D2_1,1

      D1_2,D2_1,1

      D1_2,D2_1,5

       

      D1_1,D2_2,1

      D1_1,D2_2,4

      D1_1,D2_2,4

      D1_1,D2_2,3

       

      D1_2,D2_2,1

      D1_2,D2_2,2

      D1_2,D2_2,4

      D1_2,D2_2,3

      ];

       

      In the title I want to have either (with the qsvariable extension)

      Dim1 and Dim2 for the max value,

      Dim1 and Dim2 for the max column percentage,

      Dim1 and Dim2 for the max row percentage,


      Number ==> title i want : Max value 6 for D1_1 and D2_1

                          D2_1          D2_2              

      D1_1              6                4                   

      D1_2              3                4                   

       

      Row percentage ==> title i want : Max percentage 60 for D1_1 and D2_1

                          D2_1          D2_2              

      D1_1              60%           40%

      D1_2              43%            57%                   

       

      Column pecentage ==> title i want : Max percentage 67 for D1_1 and D2_1

                          D2_1          D2_2              

      D1_1              67%            50%                   

      D1_2              33%            50%                   

       

       

      For the number i've

      ='Max value' & max(aggr(count(Value), Dim1 ,Dim2),1)

      & 'for' & only(if ( aggr(Rank(count(Value), Dim1 ,Dim2) = 1, Dim1), Dim1))

      & 'and' & only(if ( aggr(Rank(count(Value), Dim1 ,Dim2) = 1, Dim2), Dim2))

       

      witch seems working.

       

      I need some help for the two else.

       

      Thanks.

        • Re: Max percentage by row and column
          Sunny Talwar

          May be try these three

           

          ='Max value is ' & max(aggr(count(Value), Dim1 ,Dim2),1)

          & ' for ' & FirstSortedValue(Dim1, Aggr(Rank(TOTAL Count(Value)), Dim1, Dim2))

          & ' and ' & FirstSortedValue(Dim2, Aggr(Rank(TOTAL Count(Value)), Dim1, Dim2))

           

          ='Max value is ' & Num(Max(Aggr(Count(Value)/Count(TOTAL <Dim1> Value), Dim1 ,Dim2),1), '##%')

          & ' for ' & FirstSortedValue(Dim1, Aggr(Rank(TOTAL Count(Value)/Count(TOTAL <Dim1> Value)), Dim1, Dim2))

          & ' and ' & FirstSortedValue(Dim2, Aggr(Rank(TOTAL Count(Value)/Count(TOTAL <Dim1> Value)), Dim1, Dim2))

           

          ='Max value is ' & Num(Max(Aggr(Count(Value)/Count(TOTAL <Dim2> Value), Dim1 ,Dim2),1), '##%')

          & ' for ' & FirstSortedValue(Dim1, Aggr(Rank(TOTAL Count(Value)/Count(TOTAL <Dim2> Value)), Dim1, Dim2))

          & ' and ' & FirstSortedValue(Dim2, Aggr(Rank(TOTAL Count(Value)/Count(TOTAL <Dim2> Value)), Dim1, Dim2))