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

Using Aggr() and and DISTINCT

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.

Avg({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft)

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.  

ReceivableIDAllocAmtEfftDtCollEfft
1$10 8
1$50 8
1$60 8
2$40 2
3$10 5
3$10 5
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

I'm always nervous about an average of an average, but how about

Avg(Aggr(Avg({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft),ReceivableID))

View solution in original post

6 Replies
jwjackso
Specialist III
Specialist III

I'm always nervous about an average of an average, but how about

Avg(Aggr(Avg({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft),ReceivableID))

greend21
Creator III
Creator III
Author

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.

Avg(Aggr(Sum({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft),ReceivableID))

balabhaskarqlik

May be:

Avg(Aggr(Distinct Sum({$<RecOpenStatus={"Closed"}, RecCloseFlag= {1}, RecCloseCategory = {"Collected"}>} EfftDtCollEfft),ReceivableID))

sasikanth
Master
Master

HI,

Create a composite key( combination of fields to get distinct values) in the script if possible.

=Avg(Aggr(Distinct Sum({$<RecOpenStatus={"Closed"}, RecCloseFlag= {1}, RecCloseCategory = {"Collected"}>} EfftDtCollEfft), CompositeKey))

OR try

Avg(Aggr(Distinct Sum({$<RecOpenStatus={"Closed"}, RecCloseFlag= {1}, RecCloseCategory = {"Collected"}>} EfftDtCollEfft), ReceivableID,AllocAmt))


Thanks,

greend21
Creator III
Creator III
Author

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.

greend21
Creator III
Creator III
Author

When I was comparing in excel I missed filtering one thing out. This gives me the expected result.