Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How to show top 3 value based on second dimension in 100 % stacked bar chart.
Input :
A B C D
B1 T10 8987 68%
B1 T1 897 7%
B1 T4 789 6%
B1 T7 654 5%
B1 T5 589 4%
B1 T2 568 4%
B1 T3 458 3%
B1 T6 89 1%
B1 T9 89 1%
OUTPUT:
A B C
B1 T10 68%
B1 T1 7%
B1 T4 6%
Hi,
you can use the script below to replace the A field in dimension
=aggr(if( rank(Only(D),4)<=3,A), ID)
you can get results like:
the load script:
Data:
load Rowno() as ID,* inline [
A,B,C,D
B1,T10,8987,68%
B1,T1,897,7%
B1,T4,789,6%
B1,T7,654,5%
B1,T5,589,4%
B1,T2,568,4%
B1,T3,458,3%
B1,T6,89,1%
B1,T9,89,1%
];
this script also works without ID on load
=aggr(if( rank(Only(D),4)<=3,A), B)
Can you help on to do 3 column instead of 4 column to achieve same output . Ex similiar % column like 4
B1 T10 8987 10673 84.2%
B1 T1 897 10673 8.4%
B1 T4 789 10673 7.4%
I'm sorry, but I didn't really understand your request
Hi,
This is my desired Output.
A B C D E
B1 T10 8987 10673 84.2%
B1 T1 897 10673 8.4%
B1 T4 789 10673 7.4%
I still don't get it.
How do you get from this input
Input :
A B C D
B1 T10 8987 68%
B1 T1 897 7%
B1 T4 789 6%
B1 T7 654 5%
B1 T5 589 4%
B1 T2 568 4%
B1 T3 458 3%
B1 T6 89 1%
B1 T9 89 1%
this output :
A B C D E
B1 T10 8987 10673 84.2%
B1 T1 897 10673 8.4%
B1 T4 789 10673 7.4%
For example 10673 ? or 84.2% ?
10673 is the sum of three values 8987 + 897 + 789
&
84.2% is 8987/10673
Ah , I get it now., like :
to get E and F use this script
for E:
=sum(total aggr(if( rank(Only(D),4)<=3,C), B))
for F:
=num(C/sum(total aggr(if( rank(Only(D),4)<=3,C), B)),'###0,00%')
Above solution may not work if you have multiple values in A field like below
A,B,C
B1,T10,8987
B1,T1,897
B1,T4,789
B1,T7,654
B1,T5,589
B1,T2,568
B1,T3,458
B1,T6,89
B1,T9,89
B2,T1,100
B2,T11,250
B2,T3,300
B2,T4,400
This will make chart expression more complex. Hence, providing script solution
T1:
load * inline [
A,B,C
B1,T10,8987
B1,T1,897
B1,T4,789
B1,T7,654
B1,T5,589
B1,T2,568
B1,T3,458
B1,T6,89
B1,T9,89
B2,T1,100
B2,T11,250
B2,T3,300
B2,T4,400
];
left join(T1)
Load A,
B,
AutoNumber(Sum(C),A) as Rank
Resident T1
Group by A,B
Order by C desc;
Left Join(T1)
Load A,
Sum(C) as D
Resident T1
where Rank<=3
Group by A;
Now create Chart,
Dimension
A,
B
Expression:
C -
=Sum({<Rank={"<=3"}>}C)
D-
=Sum({<Rank={"<=3"}>}D)
E-
=Sum({<Rank={"<=3"}>}C)/Sum({<Rank={"<=3"}>}D)