topic Re: Getting an Average in Set Analysis in QlikView

Getting an Average in Set Analysis

Fri, 06 Jan 2017 20:26:58 GMT

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

Re: Getting an Average in Set Analysis

sunny_talwar — Fri, 06 Jan 2017 20:28:30 GMT

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

Re: Getting an Average in Set Analysis

Fri, 06 Jan 2017 20:39:05 GMT

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

Re: Getting an Average in Set Analysis

sunny_talwar — Fri, 06 Jan 2017 20:55:13 GMT

What is the expected output?

Re: Getting an Average in Set Analysis

Fri, 06 Jan 2017 21:34:08 GMT

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.

Re: Getting an Average in Set Analysis

sunny_talwar — Sat, 07 Jan 2017 03:18:49 GMT

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

Re: Getting an Average in Set Analysis

Mon, 09 Jan 2017 10:39:36 GMT

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)

Re: Getting an Average in Set Analysis

sunny_talwar — Mon, 09 Jan 2017 11:51:36 GMT

Try this:

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

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

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

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

=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

Re: Getting an Average in Set Analysis

Mon, 09 Jan 2017 12:25:06 GMT

Thank you so much for all of your help Sunny!