Show the Top 3 concatenated in a table

[Dayna]

Hi All,

I have a table with a variety of fields and I want to combine the top 3 reasons on it.

At the moment, my table data looks like this:

Name	Quantity	Speed	Result
test1	100	20	2000
test2	34	56	666
test3	12	34	45

I then have codes against these names with a duration - at the moment, the duration is calculated by some set analysis, which hopefully won't confuse matters! i.e.

Name	Code	Duration
test1	code1	10
test1	code2	20
test1	code3	50
test1	code4	40
test1	code5	5
test1	code6	100

What I want as an end result, is to rank these values and add it to my original table, i.e.

Name	Quantity	Speed	Result	Top 3
test1	100	20	2000	code 6 (100), code3 (50), code4 (40)
test2	34	56	666
test3	12	34	45

I haven't quite worked out how to add the rank concatenated, let alone the 'nice' value rather than the ranked value.

Hopefully the above makes sense, and you can help me with my query.

Many thanks in advance,

Dayna

[sunny_talwar]

Try this

=Concat(DISTINCT Aggr(If(Rank(Duration) < 4, Code & '(' & Duration & ')'), Name, Code), ', ', -Duration)

[Dayna]

Thanks for the quick response Sunny! Somewhere, there's an error in my formula, this is what I'll end up with:

=Concat(DISTINCT Aggr(If(Rank(

Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>}event.downtime_hour_duration)

) < 4, Reason.EnglishDescription2 &

'(' & Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>}event.downtime_hour_duration) & ')'), rawdata.leg, Reason.EnglishDescription2), ', ', -Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>}event.downtime_hour_duration))

[sunny_talwar · Accepted solution]

Try this

=Concat(DISTINCT Aggr(If(Rank(Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>} event.downtime_hour_duration)) < 4, Reason.EnglishDescription2 & '(' & Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>} event.downtime_hour_duration) & ')'), rawdata.leg, Reason.EnglishDescription2), ', ', -Aggr(Sum({<event.value={'STOP'}, line.devicetype= {'WRAPPER'}>} event.downtime_hour_duration), rawdata.leg, Reason.EnglishDescription2))

[Dayna]

Sunny, you're amazing! Thank you!