Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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
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.
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.