Expression not linked to table dimensions >> separated from the selection forced by dimensions

[#1]

Hi to everyone!

I have a syntax doubt. I want to know what can I do so the dimensions of a pivot table do not force a selection to the expression of the table. I searched in the community but I couldn't find anything that helps me.

I have to situations with a pivot table (which has 2 dimensions and 1 expression) where I want the expression only to be conditioned by one of the dimensions. Or say it in another way that the expression of the table is afected only by the selection one of the dimensions and excluded from the other. I don't know if I have expressed myself, so I write you one of this situations as example,

Dimensions:

- Client ranking (Using an expression)

- Product Grouping Attribute

Expression:

- Sells Percentage of the Client in each group (each value of the attribute). To do so I want to divide the Sells of the Client by the Sells of all the product group. The Sells of the Client would be: Sum(Sells) and the table dimensions would do the rest, but the syntax for the Sells of all the group... I don't know, because the table dimensions force the selection.

I can't use Sum{Total Sells} because I don't want all the Sells, and I've tried expressions where I add values to the actual selection (Like: Sum({$<[Product Group]={"*"}>Sells),... ), but the dimension selections prevail. I've also tried other expressions with {1} but I couldn't get selection

Can any one help me?

Have a nice day and thanks in advance for your time and dedication! [:)]

Josep

[#2]

Sounds to me like you should use the AGGR function because this forces the expression to move outside of the dimensions of the chart.

An example:

= SUM ( AGGR ( SUM ( Sells ) , ClientRanking ) )

Let me know if it helps.

[#3]

Thanks Nigel,

First of all, I want to point out that the Ranking is not for all the Clients but only the top 100.

if(aggr(Rank(Sum(Sells),1),Client)<101,Client)

And now I'm going to explain you my guessing of your intention: Becasue I want the Sells of each client relative to all the sells in the group (not only the ones of the sells of the top 100 clients in the group), I changed your expression for:

= SUM ( AGGR ( SUM ( Sells ) , Client ) )

And it didn't work...

One question that came to my mind, by saying the AGGR forces the expression to move outside of the dimensions of the chart, do you mean ALL the dimensions of the chart or only the one you're AGGRing?

And of course, any more ideas?

[#4]

AGGR will go outside of ALL dimensions of the chart, which is why you should put the dimensions back into the AGGR function if you want to use them.

Can you post a sample app so I can take a look?

[#5]

Ok! here goes the sample app!

[#6]

Sorry, I thought this would be an easy one! Just shows how wrong you can be.

There is of course the slightest chance that I'm being a complete numpty this afternoon and I'm missing something obvious, this seems like such a simple thing you'd want to do.

ANYBODY ELSE PLEASE!!!

[#7]

Jajaja, I feel the same so... don't worry

Come on colleagues out there!... Help!

Those of you with TOP 50 or less... You can teach a TOP 25!!

And those of you with TOP 25 or more... Show why do you have this status!!

[johnw · Accepted solution]

I couldn't figure out on brief glance which of these you want, but I think you want one of these expressions:

sum(Sell)/sum(total <Client> Sell)
sum(Sell)/sum(total <Group> Sell)

If I'm off base, I'll have to read the question more carefully.

[#9]

John

I never knew you could do that, the second one is the correct one, gives exactly the answer I was trying to find.

Thanks for hopping in on this one.

[#10]

My neither! Thanks John!

(I was making experiments with my new knowledge)