Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
How to display possible values in Text box in case of Tie (working with First sorted Value) ?
AZ:
LOAD * INLINE [
Area, Zone, Sales
A1, Z1, 400
A2, Z2, 500
A3, AB, 200
A4, ZC, 50
A1, AC, 200
A1, Z3, 100
A1, B1, 100
A3, BO, 200
A3, QV, 100
A4, Z0, 100
A4, Z7, 300
];
Area | SUM(Sales) |
A1 | 800 |
A2 | 500 |
A3 | 500 |
A4 | 450 |
Not working : CONCAT(DISTINCT FirstSortedValue(Area,-AGGR(SUM(Sales),Area),2))
Thanks & Regards
Shekar
My bad, try this
=Concat(DISTINCT Aggr(If(Sum(Sales) = Max(TOTAL Aggr(Sum(Sales), Area), 2), Area), Area), ', ')
May be this
Concat(DISTINCT Aggr(If(Sum(Sales) = Max(TOTAL Aggr(Sum(Sales), Area), 2), Area))
My bad, try this
=Concat(DISTINCT Aggr(If(Sum(Sales) = Max(TOTAL Aggr(Sum(Sales), Area), 2), Area), Area), ', ')
Thank you Sunny Bhai ,
Two quick question ,
1) can not we get something like above using first sorted value
2) Can i directly use above on very huge volume of data without worry about slowness .. asking just for alternatives
Thanks & Regards
Shekar
1) FirstSortedValue is most likely not work because it gives only a single value....
2) Alternative, if you only have a single dimension
=Concat(DISTINCT {<Area = {"=Rank(Sum(Sales), 1) = 2"}>} Area, ', ')
Another one
=Concat(DISTINCT Aggr(If(Rank(Sum(Sales), 1) = 2, Area), Area), ', ')
but I like this better if you have a single dimension
=Concat(DISTINCT {<Area = {"=Rank(Sum(Sales), 1) = 2"}>} Area, ', ')