Average of Averages that are not zero

tschullo — Sat, 16 Nov 2024 19:04:34 GMT

I need to calculate the average of average Hours spent on audits by Lead Team .

The added caveat that is missing is, don't count any Lead Teams where no time was spent on audits.

I am displaying the team average hours per audit as bars. So if I had a bar1= 50, a bar2= 100, and a bar3= 150, the average would be 100 because the sum of the 3 is 300 divided by 3 gives me an average of 100.

The issue occurs when one of the bars has a zero value . In QlikView that bar can be suppressed (as in Qlik Sense as well) but the QV average reference line automatically takes it out of the equation.

So for example if bar3= 0 and therefore suppressed, QV would sum the 2 non zero bars as 150 and divide by 2 to get an average of 75. In QS, it is still dividing the sum of all 3, 150 by 3 and getting 50.

How do I get QS reference line to behave like QV?

My current expression is:

=Avg(Total Aggr(
Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours)/
Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])
, [Lead Team]))

Thanks!

topic Average of Averages that are not zero in App Development

Average of Averages that are not zero