Average and Aggr formulas

mikecherry — Sat, 16 Nov 2024 06:10:16 GMT

Hi all,

I am trying to calculate an Average in one line but, because I am filtering and at the same time avoiding that filter the AGGR formula doesn't work properly .

This is my actual table and the expression is:

Called:

-SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Cost"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Commitment"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

vBenchmarkField= Concat(DISTINCT Benchmark_Key, ', ')

I want to calculate the AVG of these funds removing the Fund_Name column having basically one line showing the Average Called %.

I have tried :

avg(aggr( SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Dist"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

-SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Cost"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure)),Benchmark_Key,Fund_Name))

but as soon I put the Fund_Name in the aggr formula is basically overriding my "Fund_Name=" in set analysis and showing me only the value for that fund I have selected.

Any thoughts ?

Re: Average and Aggr formulas

sunny_talwar — Wed, 03 Apr 2019 11:55:27 GMT

Try this

Avg({<Fund_Name>} Aggr(
Sum({<Data_Type = {"Trans"}, Fund_Name, Benchmark_Key = {"$(vBenchmarkField)"}, Data_Measure = {"Dist"}, Date_ID = {"<=$(vReportDate)"}>} $(vMeasure))
/
-Sum({<Data_Type = {"Trans"}, Fund_Name, Benchmark_Key ={"$(vBenchmarkField)"}, Data_Measure = {"Cost"}, Date_ID = {"<=$(vReportDate)"}>} $(vMeasure))
, Benchmark_Key, Fund_Name))

Re: Average and Aggr formulas

mikecherry — Wed, 03 Apr 2019 13:22:07 GMT

Thank you so much ! As usual you are the best.. 😄

topic Re: Average and Aggr formulas in App Development

Average and Aggr formulas

Re: Average and Aggr formulas

Re: Average and Aggr formulas