Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I'm always nervous about an average of an average, but how about
Avg(Aggr(Avg({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft),ReceivableID))
I'm always nervous about an average of an average, but how about
Avg(Aggr(Avg({$<RecOpenStatus={'Closed'}, RecCloseFlag= {1}, RecCloseCategory = {'Collected'}>} EfftDtCollEfft),ReceivableID))
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))
May be:
Avg(Aggr(Distinct Sum({$<RecOpenStatus={"Closed"}, RecCloseFlag= {1}, RecCloseCategory = {"Collected"}>} EfftDtCollEfft),ReceivableID))
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,
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.
When I was comparing in excel I missed filtering one thing out. This gives me the expected result.