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

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

Thu, 13 May 2010 09:37:32 GMT

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

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

Thu, 13 May 2010 10:11:34 GMT

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.

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

Thu, 13 May 2010 10:39:25 GMT

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?

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

Thu, 13 May 2010 11:30:33 GMT

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?

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

Thu, 13 May 2010 12:18:46 GMT

Ok! here goes the sample app!

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

Thu, 13 May 2010 16:03:00 GMT

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!!!

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

Thu, 13 May 2010 17:35:17 GMT

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!!

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

johnw — Thu, 13 May 2010 19:30:55 GMT

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.

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

Fri, 14 May 2010 09:27:58 GMT

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.

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

Mon, 17 May 2010 09:00:05 GMT

My neither! Thanks John!

(I was making experiments with my new knowledge)