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