Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
Thanks a lot Sunny, It's seems to be right!!!!
Howerver a question :
What does <Dim1> means in TOTAL <Dim1> Value ?
Those are used to determine which dimension are you looking to total by. Total by Dim1 or Dim2