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

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Wed, 28 Mar 2018 11:43:14 GMT

May be this

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

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

sunny_talwar — Wed, 28 Mar 2018 11:44:43 GMT

My bad, try this

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

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

shekhar_analyti — Wed, 28 Mar 2018 12:39:20 GMT

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

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

sunny_talwar — Wed, 28 Mar 2018 12:43:46 GMT

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

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

sunny_talwar — Wed, 28 Mar 2018 12:44:50 GMT

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