Getting an Average in Set Analysis

[#1]

Hello:

I am trying to find the average of days, and I know I need more on my Set Analysis, but noting I do is returning the correct value.  I have dropped the set analysis back down to the bare minimum for this post.  I need to find the average of [Issue Age], and need to keep the Distinct on the Issue ID and also maintain the Load Date portion of the set analysis as well.

Is it possible?

=Num(Avg(DISTINCT{<[Issue Age]={'>0'},[Load Date] = {'$(=Max([Load Date]))'}>} [Issue ID])/[Issue Age],'##,###')

Thank you in advance for your help

[sunny_talwar]

Would you be able to share few rows of data with the output you expect to see from the input provided?

[#3]

  Hi Sunny, I just created some data for this string. This is the type of data I am working with.  

Load Date	Issue ID	Issue Age
12/29/2016	1234	224
12/29/2016	22255	123
12/29/2016	1234	782
12/29/2016	44448	52
12/29/2016	55556	235
12/29/2016	88821	224
12/29/2016	66994	774
12/29/2016	66994	145
12/29/2016	78945	123
12/29/2016	44877	782
12/29/2016	44876	52
12/29/2016	44879	235
12/29/2016	55514	224
12/15/2016	22255	116
12/15/2016	1234	775
12/15/2016	44448	45
12/15/2016	55556	228
12/15/2016	88821	217
12/15/2016	66994	767
12/15/2016	66994	138
12/15/2016	78945	116
12/15/2016	44877	775
12/15/2016	44876	45
12/15/2016	44879	228
12/15/2016	55514	217
12/15/2016	22212	925
12/15/2016	23461	871
12/15/2016	44487	1112
12/8/2016	22255	109
12/8/2016	1234	768
12/8/2016	44448	38
12/8/2016	55556	221
12/8/2016	88821	210
12/8/2016	66994	760
12/8/2016	66994	131
12/8/2016	78945	109
12/8/2016	44877	768

[sunny_talwar]

What is the expected output?

[#5]

I am looking to get an average of issue Age, based on the Load Date.  The Average, needs to be distinct, since there are duplicate Issue IDs.

We use Load date as a main  filter in all of our tables in QV.  We use Max Load Date, but also have a filter to be able to look at previous load dates, which is why the calc has Load Date and Max Load date included.

[sunny_talwar]

Within your sample we have Issue ID 66994 repeating two times. How would you use this information here? Sum the Issue Age and then find the average Issue Age?

For the Load Date = 12/29/2016 (Max Date), I get 361.36364. Is this what you expect to see

Expression used

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Sum({<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

[#7]

Hello Sunny:

I apologize, the dups for the same date should have the same number of days associated with them.

The formula you provided is not working, it is not removing the duplicates. (Which is an issue with the data I provided as a sample, as stated above)

[sunny_talwar · Accepted solution]

Try this:

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Only({<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

or

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Sum(DISTINCT {<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

or

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Max({<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

or

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Min({<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

or

=Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>}Aggr(Avg({<[Load Date] = {"$(=Date(Max([Load Date])))"}>} [Issue Age]), [Issue ID]))

all should provide the same result

[#9]

Thank you so much for all of your help Sunny!