Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Get the average of a field based on distinct values in other field

Hi Community,

I am struggling to get the average of 'Metric' where Grades are distinct. I am using below set expression to get the average of Metric. This is working fine, but the issue comes when I have duplicate grades. I don't want duplicates while calculating the average. This is the sample data. Grade 979720 is duplicate.

   

GradesFBI Financial statement dateMetric
9373892014-09-30 00:00:0027850000
9797202015-03-31 00:00:0070161000
9797202015-03-31 00:00:0070161000
9938142015-03-31 00:00:0037764000
9992622014-12-31 00:00:00842598000
9993392015-03-31 00:00:0018149000

So when I use:

avg
({<
[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"}
>} 
Metric)

I get  177780500 as average.

While calculating the average it has to consider 979720 only once, so that the actual result is: 199304400

Need your help!!

   




1 Solution

Accepted Solutions
sunny_talwar

How about this?

Avg(Aggr(

Only
({<[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"}>} Metric),

Grades, [FBI Financial statement date]))

View solution in original post

7 Replies
sunny_talwar

May be try this:

Avg(Aggr(

Only
({<[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"}>} Metric),

Grades))

Anonymous
Not applicable
Author

Hi Sunny,

This expression does not return me any thing. Blanks

sunny_talwar

How about this?

Avg(Aggr(

Only
({<[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"}>} Metric),

Grades, [FBI Financial statement date]))

Anonymous
Not applicable
Author

No not returning any thing. Can we filter it in the set modifier like I have done for other fields?

sunny_talwar

Not sure why it might not be working for you, it works for me

Capture.PNG

Anonymous
Not applicable
Author

Hi Sunny,

It worked perfectly. I was missing a dimension while doing aggregation. My doubt is with ONLY function. While doing some analysis I found that when ever there is a non distinct value ONLY will return Null. Than how is it working in the above context.

Thanks

sunny_talwar

For a particular combination of Grade and FBI Financial statement date, if you will only have one and only one value of metric (the value itself can repeat multiple times, but the number should be the same), the only function will return the correct value. If in case you had this:

9797202015-03-31 00:00:0070161000
9797202015-03-31 00:00:00121212121

Instead of the above sample, you will run into issue. Is this a possibility? If it is how do you want to handle this?