# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for
Did you mean:
Highlighted
Contributor

## Aggr function

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?

Labels (1)
• ### aggr

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary

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

4 Replies
Highlighted
MVP

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)?

Highlighted
Contributor

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

Highlighted
MVP & Luminary

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

Highlighted
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.