Avg of ratios (including nulls)

[YoussefBelloum]

Hi guys, 

I'm struggling on including Null values on the calculation of my average..

I'm calculating S/P = Sinistre/Prime => sum(sinistre)/sum(Prime) for each year using set analysis.

I'm trying to calculate the avg S/P (On 5 years), the problem is that for 2018, Prime=0, it give NULL on the S/P 2018 and the average functions ignore nulls by default.. and I want to include it to get this:

41,67+18,18+30+44,44+NULL = 134,29 ==> I Need 134,29 / 5 = 26,85 % and not 134,29/4 = 33,57%

I tried using Alt(), using avg(aggr(sum... 

Thank you for your help.

Youssef

[YoussefBelloum · Accepted solution]

This works fine:

Avg(Aggr(Alt(sum({<Année={">=$(Max(Année)-4)<=$(=Max(Année))"}>}sinsitre)/sum({<Année={">=$(Max(Année)-4)<=$(=Max(Année))"}>}prime),0),CR,Année))

I was only aggregating on the CR field and missing the Year field (Année)..

Thanks anyway !

[sugathirajkumar]

count(prime)? still clarity is needed