Aggr function

[Jordi_Fo]

Hi Everyone, 

Given the following table:

Plant	Code1	Code2	Number
H	MA002	200BH90	526,3
H	MA120	200BH90	526,3
H	MA210	200BH90	526,3
H	MA230	200BH90	526,3
H	MA600	200BH90	526.3
H	MA120	240BDH90	658,8
H	MA210	240BDH90	658,8
H	MA230	240BDH90	658,8
H	MA600	240BDH90	658.8
H	MA002	240BH3	98,5
H	MA120	240BH3	98,5
H	MA210	240BH3	98,5
H	MA230	240BH3	98,5
H	MA600	240BH3	98,5

The value of the field Number unfortunately repeats for every line. I would like to calculate the sum of Number for every code2.  So the result in that case it would be 526.39 + 658.8+98.5 = 989.88

First, I tried using Distinct function

sum( distinct(Number))

The result was correct until the value of number repeated between two different Code2.

The next attempt was using the function AGGR.

sum(aggr(sum(((Number))), Code2))

This function calculates the correct total value, but I’m not able to get the original table through  the aggr formula.

What is missing?

[tresesco]

Not sure if understood right. Try the expression like:

sum(aggr(NODISTINCT sum(((Number))), Code2))

If this doesn't help, try to explain a bit more about your expected output (where/how)?

[Jordi_Fo]

Hello Tresesco,

Hopefully the following description will be clear. 

As you can se the table has duplicated values in the field NUMBER. 

Using nothing but the sum function I get the following table

Plant	Code1	Code2	Number
H	SUM		3.947,52
H	MA002	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA120	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA210	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA230	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99

In that case the sum result wis wrong.  It must be 526.39 + 658.8+98.5 = 989.88

To solve this problem I tried with distinct

sum( distinct(Number))

Plant	Code1	Code2	Number
H	SUM		986,88
H	MA002	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA120	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA210	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99
H	MA230	SUM	986,88
H	MA002	200BH90	526,39
H	MA002	240BH3	98,50
H	MA002	240BH90	361,99

That solved the problem partially.

In case 200BH90 and 240BH3 get the same number, the sum will give a wrong number. 

So I tried using AGGR in order to group the sum by code2.But th results were wrong as well. 

How I could get the table with a correct SUM but been non dependent of the value of NUMBER

[marcus_sommer · Accepted solution]

You could try something like:

sum(aggr(avg(Number), Code2))

But for me it looked more that there is anything not very suitable merged/transformed within the datamodel. Nevertheless if this structure is unavoidable I would tend  to create an additionally counter-field to divide the values through this number of occurrence value (preferable within the script, too) so that you in the end could use a simple sum().

- Marcus

[Brett_Bleess]

Not sure if Marcus' last post helped or not, if it did, do not forget to give credit by using the Accept as Solution button on his post.  Here is a Design Blog post that may be of some further help:

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If you want to search further on your own, try the following base link to do that:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett