Pivot Chart

[#1]

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

[#2]

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

[#3]

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

.

[#4]

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

[#5]

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

[#6 · Accepted solution]

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

[#7]

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

[#8]

Hi Suchith,

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

RR