topic Pivot Chart in QlikView

Pivot Chart

Sun, 23 Jan 2011 15:31:39 GMT

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.

i request your assistance.

regards

AW:Pivot Chart

Sun, 23 Jan 2011 17:21:40 GMT

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

Mon, 24 Jan 2011 09:57:38 GMT

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

Mon, 24 Jan 2011 13:55:22 GMT

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

Tue, 25 Jan 2011 10:15:06 GMT

Roland,

Thanks for your support

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.

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

Tue, 25 Jan 2011 12:06:02 GMT

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)

AW:Re: AW:Re: AW:Pivot Chart

Tue, 25 Jan 2011 13:30:48 GMT

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

Tue, 25 Jan 2011 15:49:24 GMT

Hi Suchith,

glad to help you. Would you also validate my post above as answer.