aggr question HELP ME!!

[#1]

Hi all,

in this example I whish to write on gauge caption the name of the best PRD, and to find the best PRD disregarding the selection.

Any suggestion?

Thanks a lot!

[martin59 · Accepted solution]

Hi Roberta,

You will find the solution of your problem on the attachment.

I hope that will be useful for you.

Martin Favier

[#3]

Great!

Thank you Martin!!!!

[#4]

Hi Martin

Tried to come up with a solution but failed - can't even fully understand your approach - can you elaborate on this?

And - with your obvious skills - look from time to time into the forum? 7 points are not very much?

Regards

Juerg

[martin59]

Hi Juerg,

Here is the detail of my formula with current selections :

subfield(concat(aggr(rank(sum(total <PRD> IMP1)) & '#' & PRD & '#',PRD)),'#',2)

1. sum(total <PRD> IMP1) // We calculate the sum of IMP1 per PRD, TOTAL allows to work with all PRD included in active selections

2. rank(sum(total <PRD> IMP1)) & '#' & PRD & '#' // We sort the PRD with the others and concatenate with "#" and PRD's name

3. aggr(rank(sum(total <PRD> IMP1)) & '#' & PRD & '#',PRD) // We doing this treatment for each PRD

4. concat(aggr(rank(sum(total <PRD> IMP1)) & '#' & PRD & '#',PRD)) // We concatenate each response and have a result looks like this :

1#PRD A#2#PRD E#

5. subfield(concat(aggr(rank(sum(total <PRD> IMP1)) & '#' & PRD & '#',PRD)),'#',2) // We divide the result string for each sharp, and take the second value (PRD A)

I hope to have been clear and I'll try to look more often the forum 😉

Have a good day,

Martin

[#6]

Thanks Martin for the explanations. Ran quite a few variations on it to see whether it must be that complicated but only your solution worked so far.

See that you started to make lots of points