topic Re: Aggr() Issue in QlikView

Aggr() Issue

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Aggr() Issue

sunny_talwar — Tue, 10 Jul 2018 18:03:20 GMT

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

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

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

Sum(Aggr(Charge, ID))

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

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

Re: Aggr() Issue

greend21 — Tue, 10 Jul 2018 18:28:28 GMT

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.

Re: Aggr() Issue

vishsaggi — Tue, 10 Jul 2018 18:58:07 GMT

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.

Re: Aggr() Issue

greend21 — Wed, 11 Jul 2018 12:40:06 GMT

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?

Re: Aggr() Issue

greend21 — Wed, 11 Jul 2018 20:20:06 GMT

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

Re: Aggr() Issue

greend21 — Wed, 18 Jul 2018 15:36:09 GMT

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.