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

# Max percentage by row and column

Hy,

I've data like this

New_Table:

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

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))

• ###### Re: Max percentage by row and column

Thanks a lot Sunny, It's seems to be right!!!!

Howerver a question :

What does <Dim1> means in TOTAL <Dim1> Value ?

• ###### Re: Max percentage by row and column

Those are used to determine which dimension are you looking to total by. Total by Dim1 or Dim2