Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Aggr function

Hi Everyone, 

Given the following table:

PlantCode1Code2Number
HMA002200BH90526,3
HMA120200BH90526,3
HMA210200BH90526,3
HMA230200BH90526,3
HMA600200BH90526.3
HMA120240BDH90658,8
HMA210240BDH90658,8
HMA230240BDH90658,8
HMA600240BDH90658.8
HMA002240BH398,5
HMA120240BH398,5
HMA210240BH398,5
HMA230240BH398,5
HMA600240BH398,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)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
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

View solution in original post

4 Replies
Highlighted
MVP
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
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

PlantCode1Code2Number
HSUM 3.947,52
HMA002SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA120SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA210SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA230SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,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))

PlantCode1Code2Number
HSUM 986,88
HMA002SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA120SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA210SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,99
HMA230SUM986,88
HMA002200BH90526,39
HMA002240BH398,50
HMA002240BH90361,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
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

View solution in original post

Highlighted
Digital Support
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.