7 Replies Latest reply: Jan 25, 2011 10:49 AM by Roland Kunle

# Pivot Chart

hello guy,

i have posted a question few days back and did not recived any reply i think its because of the wrong heading which i have given.

i am posting the same the question but with correct header and more details.

i have a pivot chart with two dimensions [Trade Name] and [Sector] and expression which as below

=

SUM(aggr(if(rank (sum({<Type={"IMPORT"},[Cargo Type]={"Rf"}>} Potential))<=10,sum({<Type={"IMPORT"},[Cargo Type]={"Rf"}>}Potential)),[Trade Name],Sector

))

By using this am geting all the trade names, irrespective of the top 10. below is the screen shot of the result. what i want is only the top 10. trade name.

regards

• ###### AW:Pivot Chart

Hello,

the SET analysis within your expression filters only the sum() of your expression. So your sum may be as you expect it, but you need this filter in the corresponding dimension as well.

For this I would replace the old Trade-Dim with a calculated Dimension Trade similar to your expression:

`if(rank (sum({<Type={"IMPORT"},[Cargo Type]={"Rf"}>} Potential))<=10,[Trade Name])`

Note: I copied parts from your post above, pls check the syntax.

Regards, Roland

• ###### AW:Pivot Chart

Thank Roland for the prompt suggetion.

The formula worked, only thing i added was aggr function to that formula.

Now I want to get top ten TRADES and then top five SECTORS for that TRADE.

I tried to put calculated dimension for sector as well but its not giving me the desired output.

below is the screen shot

• ###### AW:Re: AW:Pivot Chart

Hello again,

`suchith711 wrote:The formula worked, only thing i added was aggr function to that formula.`

Nice to hear that it is working. OK, I saw that you are familar with aggr(). ;-)

Now you are not far away from the second expression. Did you try:

`=if( aggr(rank( aggr(sum(Potential), TRADE, SECTORS ), 4, 2), TRADE, SECTOR ) <= 5, SECTOR )`

Regards, Roland

• ###### AW:Re: AW:Pivot Chart

Roland,

I think I am close to the target. It's frustrating when you see the target and miss it L.

I think its little more complex than what I was thought.

http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/3731.Pivot-Table.JPG

Referring to my attachment, this is the result what I am looking for, but I want EXPR1(I kept your formula) the "expression" to be in dimension instead of sector.

I have "VEGETABLES & FRUITS TRADE" for all SECTOR as 4230, but when I add top five sector then it takes the total of the top 5 sectors so the order is gone. Now "FISH TRADE" is showing as the top TRADE, but actually I want TOP 10 customer irrespective of the SECTOR and from that the TOP 5 sectors.

sorry for troubling you so often,

Regards,Suchith

• ###### AW:Re: AW:Re: AW:Pivot Chart

OK Suchith,

I am sure you are very close. Use it as dimension, replace your sector-dim with the calc dim "sector". Then take a look at your sorting of your Trade-Dim: Check "expression" on the sort tab and use the filter part of your calculated Trade-dim. It should look similar to

`rank( aggr(sum(Potential), TradeName), 4, 2)`

RR

• ###### AW:Re: AW:Re: AW:Pivot Chart

hello Roland,

At last with your help i solved the issue. i never knew that even in sort we can put the functions, even though i have seen it expression in the sort tab.

so today i have lerned one more thing in QV.

Thank you very much for the support.

regards,suchith

• ###### AW:Re: AW:Re: AW:Re: AW:Pivot Chart

Hi Suchith,