Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Grades | FBI Financial statement date | Metric |
937389 | 2014-09-30 00:00:00 | 27850000 |
979720 | 2015-03-31 00:00:00 | 70161000 |
979720 | 2015-03-31 00:00:00 | 70161000 |
993814 | 2015-03-31 00:00:00 | 37764000 |
999262 | 2014-12-31 00:00:00 | 842598000 |
999339 | 2015-03-31 00:00:00 | 18149000 |
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!!
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]))
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))
Hi Sunny,
This expression does not return me any thing. Blanks
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]))
No not returning any thing. Can we filter it in the set modifier like I have done for other fields?
Not sure why it might not be working for you, it works for me
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
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:
979720 | 2015-03-31 00:00:00 | 70161000 |
979720 | 2015-03-31 00:00:00 | 121212121 |
Instead of the above sample, you will run into issue. Is this a possibility? If it is how do you want to handle this?