Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
I moved the formula from calculate dimension to expression and all work now, thanks HD.