Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

Aggr() Issue

I've seen examples online of how to sum totals per distinct ID but unless I am missing something it is not working for me. If I sum charge I correctly get $244,102,899.29, just as in excel. If I remove duplicate ID's in excel I get $244,082.019.66 but I cannot get this number within Qlik. Can anyone help?

6 Replies
sunny_talwar

Not sure what expression you tried, but try one of these

Sum(Aggr(Sum(DISTINCT Charge), ID))

or

Sum(Aggr(Avg(Charge), ID))

or

Sum(Aggr(Charge, ID))

or

Sum(Aggr(Min(Charge), ID))

or

Sum(Aggr(Max(Charge), ID))

greend21
Creator III
Creator III
Author

A few of these all get me the same result if I filter on one ID, for example I get $618.40 for 66761, but when I run it for the the entire I do not receive the correct number.

vishsaggi
Champion III
Champion III

What you mean when you run it for entire? What is that you are expecting as your output based on selection. Sunny's expr should work, unless your requirement is different? Please let us know.

greend21
Creator III
Creator III
Author

Based on the information in columns I and J in my sample data where I had excel remove duplicates I would expect Qlik to show the same $244,082,019.66 but it does not. If you use the data in Column A and B and look at ID 66761 and just summed you would get $1236.80 which is wrong. Sunny's expression gets me the correct $618.40 if I happen to filter on this receivable but it does not give me $244,082,019.66 with no selections. Aggr should give me the amount per unique ID right?

greend21
Creator III
Creator III
Author

Were you able to get it to work correctly with the sample data? I'm still not having any luck.

greend21
Creator III
Creator III
Author

I have a few spots where this is not working as expected. Does anyone have any suggestions or fixes using the sample data? I'm still not having any luck.

To clarify, I am only showing the grand total of charges and not the charge per ID in a table, but I need the duplicates removed from the total. Aggr seems to work per ID but not on a grand total.