Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with aggregate expression in a table

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 MayorImporte
Spain6100010
Spain6200020
Brazil6100030

QlikView table result:

CeBesum ( 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.

3 Replies
Not applicable
Author

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 MayorImporte
Spain6100010
Spain6200020
Brazil6100030

QlikView table result:

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

Desired table:

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

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 = 40                                                  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

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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 IDImporte

A

6100020
B6100010
B6200030
C63000

40

I obtain the following QlikView table:

CeBesum(aggr(sum(Importe),[Cuenta Mayor ID]))
A

30

B

30

C

40

And I want:

CeBe
sum(aggr(sum(Importe),[Cuenta Mayor ID]))
A20
B40
C40

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.