I have the below expression but in my data the Receivable ID's can be listed multiple times depending on the number of transactions that receivableID is involved in. The calculation for EfftDtCollEfft, which is in the load script, will result in the same number for each record of a receivableID which is fine. The issue is the values are weighted so I would like it to look at the EfftDtCollEfft per distinct receivableid.
For example the data could look like this chart. I would want my Avg() to be 5, not 6. Could you offer any help with the Aggr function? I was trying it but when I compare the numbers to excel they do not look right in Qlik.
This doesnt give the expected result either compared to excel but it is close. Using the average of the sum brings me to what I calculated in Excel before I remove the duplicate values for receivableID.
I don't think the composite key would help because the issue is I want the duplicate ReceivableID's removed. If I was able to create a composite key then duplicates of the ReceivableID would each have their own unique key and would still be counted.
Your second suggestion got the number closer. I'm not sure if it is a coicidence but maybe I can work off of that.