Max percentage by row and column

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

Howerver a question :

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

[sunny_talwar]

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