Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Tags (3)
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: Using Aggr() and and DISTINCT

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
Highlighted
Specialist
Specialist

Re: Using Aggr() and and DISTINCT

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

Highlighted
Creator III
Creator III

Re: Using Aggr() and and DISTINCT

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))

Highlighted

Re: Using Aggr() and and DISTINCT

May be:

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

Highlighted
Specialist III
Specialist III

Re: Using Aggr() and and DISTINCT

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,

Highlighted
Creator III
Creator III

Re: Using Aggr() and and DISTINCT

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.

Highlighted
Creator III
Creator III

Re: Using Aggr() and and DISTINCT

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