Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
mikecherry
New Contributor

Average and Aggr formulas

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, ', ')

 

Becnhmark.png

 

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 ?

1 Solution

Accepted Solutions

Re: Average and Aggr formulas

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))
2 Replies

Re: Average and Aggr formulas

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))
mikecherry
New Contributor

Re: Average and Aggr formulas

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