Rank or Aggr

[#1]

Hello,

I have this pivot table, which i use to calculate the total sum of turn over by product :

Product; Sum(TO)
P1; 42
The sum(TO) is given by add the (TO) of all companies.

For P1:
Sum(TO) = Sum(TO)(of company1) + Sum(TO)( of company2) + Sum(TO)(of company3)

With:
Sum(TO)(company1)= 12

Sum(TO)(company2)=20

Sum(TO)(company3)=10.

I want now, to display the company with the best "Best TO", in may example : company2. And my new pivot table will look like:

'Product'; S'um (TO)';' Best TO company'
P1; 42; 'company2'

I can find the best max (TO) with : max(aggr(sum([T/O]),Company)) but i didnt fin a way to display the name of the company!

Any one has made such application then please do forward it to me.

[hdonald]

Hi,

Perhaps both Aggr and Rank together will work - I've a related problem and have just been reading a similar post on getting max city names by population,

see http://community.qlik.com/forums/t/33417.aspx

So try something like the following;

1) Create a calculated dimension and suppress null values, label 'Best TO Company'

2) set expression = aggr( IF ( Rank(max(aggr(sum([T/O]),"Company")),1,1)=1,"Company"),"Company)"

This works on my solution but seems a bit complex - maybe someone else has a simpler solution,

Regards,

HD

[#3]

Thanks a lot HD for your poste, this solution work i can now display the company, But i have lost the total sum(TO), now i have only SUM(TO) of the best company?

[#4]

I moved the formula from calculate dimension to expression and all work now, thanks HD.