How to display possible values in Text box in case of Tie (working with First sorted Value) ?

[shekhar_analyti]

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

[sunny_talwar]

May be this

Concat(DISTINCT Aggr(If(Sum(Sales) = Max(TOTAL Aggr(Sum(Sales), Area), 2), Area))

[sunny_talwar · Accepted solution]

My bad, try this

=Concat(DISTINCT Aggr(If(Sum(Sales) = Max(TOTAL Aggr(Sum(Sales), Area), 2), Area), Area), ', ')

[shekhar_analyti]

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

[sunny_talwar]

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

[sunny_talwar]

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