Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nicolas_sinquin
New Contributor III

Max percentage by row and column

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.

1 Solution

Accepted Solutions
MVP
MVP

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

3 Replies
MVP
MVP

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

nicolas_sinquin
New Contributor III

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 ?

MVP
MVP

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