Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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))
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))