Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
greend21
Contributor II

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

Re: Aggr() Issue

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
Contributor II

Re: Aggr() Issue

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
Esteemed Contributor III

Re: Aggr() Issue

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
Contributor II

Re: Aggr() Issue

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
Contributor II

Re: Aggr() Issue

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

greend21
Contributor II

Re: Aggr() Issue

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.