topic Re: Using Aggr() and and DISTINCT in QlikView

Using Aggr() and and DISTINCT

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

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.

ReceivableID	AllocAmt	EfftDtCollEfft
1	$10	8
1	$50	8
1	$60	8
2	$40	2
3	$10	5
3	$10	5

Re: Using Aggr() and and DISTINCT

jwjackso — Tue, 03 Jul 2018 19:00:05 GMT

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

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

Re: Using Aggr() and and DISTINCT

greend21 — Tue, 03 Jul 2018 19:04:25 GMT

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

Re: Using Aggr() and and DISTINCT

BalaBhaskar_Qlik — Wed, 04 Jul 2018 08:22:00 GMT

May be:

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

Re: Using Aggr() and and DISTINCT

sasikanth — Wed, 04 Jul 2018 09:44:21 GMT

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,

Re: Using Aggr() and and DISTINCT

greend21 — Thu, 05 Jul 2018 12:13:32 GMT

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.

Re: Using Aggr() and and DISTINCT

greend21 — Thu, 05 Jul 2018 12:46:28 GMT

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