topic aggr question HELP ME!! in QlikView

aggr question HELP ME!!

Tue, 04 May 2010 13:41:42 GMT

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!

Re. :aggr question HELP ME!!

martin59 — Tue, 04 May 2010 16:50:49 GMT

Hi Roberta,

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

I hope that will be useful for you.

Martin Favier

Re. :aggr question HELP ME!!

Tue, 04 May 2010 17:38:29 GMT

Great!

Thank you Martin!!!!

Re. :aggr question HELP ME!!

Tue, 04 May 2010 19:23:59 GMT

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

Re. :Re: Re. :aggr question HELP ME!!

martin59 — Wed, 05 May 2010 08:18:19 GMT

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

Re. :Re: Re. :aggr question HELP ME!!

Wed, 05 May 2010 18:45:53 GMT

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