topic Re: Problem with aggregate expression in a table in QlikView

Problem with aggregate expression in a table

Thu, 31 Jan 2013 17:22:45 GMT

Hello everybody,

I have a problem with an aggregate expression wich is in a table.

The expression is structured as follows: Expression = sum ( aggr ( sum(Importe) , [Cuenta Mayor] ))

[Cuenta Mayor ID] is the minimum level of the data set.

Data set form:

CeBe	Cuenta Mayor	Importe
Spain	61000	10
Spain	62000	20
Brazil	61000	30

QlikView table result:

CeBe	sum ( aggr ( sum(Importe) , [Cuenta Mayor] )
Spain	60

Desired table:

CeBe	sum ( aggr ( sum(Importe) , [Cuenta Mayor] )
Spain	30
Brazil	20

The expression is a great deal more complex, since, in my dashboard, the sum which is inside the aggregate is an extremely complex computation which depends on the field [Cuenta Mayor] ]that forces me to use the aggregate function. If it was a simple sum I wouldn't use the aggregate function, but it presents quite well my problem. But that is not the point of the question.

The problem of that expression is that the aggregate does not filter within the dimension of my Table, it makes the sum of all the [Cuenta Mayor], and assigns it to its corresponding country. If, within the table, I filter by clikcing one country, it gives me the correct result since the dataset of the table is reduced to the corresponding country.

Internally, QlikView does the following:

61000 => sum(Importe) = 10+30 = 40

62000 => sum(Importe)= 20 = 20

THEN: 61000 and 62000 corresponds to Spain => THUS=> Spain = 40+20= 60

61000 corresponds to Brazil => THUS => Brazil = 20 WHICH IS NOT WHAT I WANT

Is there any way to indicate wihtin an aggregate expression to filter its computations wihtin the dimension of the table, WITHOUT including the dimension CeBe? A possible solution would be to include CeBe in the aggregate sum (aggr(sum(Importe),[Cuenta Mayor],CeBe)), but this consumes a lot of ressources and the performance worsens a lot since the data model is huge.

Does anyone know an alternative solution to indicate in the aggregate that it must filter its computations wihtin the dimension of the table?

Thanks in advance!

I am looking forward to hearing from your suggestions.

Best regards.

Re: Problem with aggregate expression in a table

Thu, 31 Jan 2013 17:26:07 GMT

Sorry!!!, I made two mistakes when summing, that is the correct statement. Ignore the first:

Hello everybody,

I have a problem with an aggregate expression wich is in a table.

The expression is structured as follows: Expression = sum ( aggr ( sum(Importe) , [Cuenta Mayor] ))

[Cuenta Mayor ID] is the minimum level of the data set.

Data set form:

CeBe	Cuenta Mayor	Importe
Spain	61000	10
Spain	62000	20
Brazil	61000	30

QlikView table result:

CeBe	sum ( aggr ( sum(Importe) , [Cuenta Mayor] )
Spain	60

Desired table:

CeBe	sum ( aggr ( sum(Importe) , [Cuenta Mayor] )
Spain	30
Brazil	30

Internally, QlikView does the following:

61000 => sum(Importe) = 10+30 = 40

62000 => sum(Importe)= 20 = 20

THEN: 61000 and 62000 corresponds to Spain => THUS=> Spain = 40+20= 60

61000 corresponds to Brazil => THUS => Brazil = 40 WHICH IS NOT WHAT I WANT

Does anyone know an alternative solution to indicate in the aggregate that it must filter its computations wihtin the dimension of the table?

Thanks in advance!

I am looking forward to hearing from your suggestions.

Best regards

Re: Problem with aggregate expression in a table

Gysbert_Wassenaar — Thu, 31 Jan 2013 19:53:34 GMT

Your sum(aggr(sum),SomeExpression) returns only 1 value since you're using only one 'dimension' to aggregate on. That's why you need to include CeBe. If you don't want that I think your only option is to calculate the aggregated table in the script. That will make your expression a lot simpler, i.e. sum(something). It will also push the performance penalty to the reload time instead of getting hit by it in the User Interface.

Re: Problem with aggregate expression in a table

Fri, 01 Feb 2013 12:43:35 GMT

Hi Gisbert,

Thanks for your quick response!!

I don't understand one thing of your explanation.

Why do you state this expression returns only one value? I thought in this case it returned two values, one for each CeBe (in the table) since it is in a table, and it associated the value obtained for each [Cuenta Mayor ID] to its country without filtering wihtin the dimension of the table.

For exemample, if the dataset is:

CeBe	Cuenta Mayor ID	Importe
A	61000	20
B	61000	10
B	62000	30
C	63000	40

I obtain the following QlikView table:

CeBe	sum(aggr(sum(Importe),[Cuenta Mayor ID]))
A	30
B	30
C	40

And I want:

CeBe	sum(aggr(sum(Importe),[Cuenta Mayor ID]))
A	20
B	40
C	40

The problem here appears when the minimum level of the dataset, [Cuenta Mayor ID] is in more than one CeBe (like 61000), and the aggregate does not filter within the table, and I don't know how to solve that. I have attempted to do it in the script but the computation depends on three time variables and it is unviable to do it there.

If the relation between [Cuenta Mayor ID] and CeBe is 1:1 then it works fine since no filter is required. I think the problem stems form the cardinality of the fields.

I think I did not understand at all your answer.