Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
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.
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.
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?
Were you able to get it to work correctly with the sample data? I'm still not having any luck.
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.