Exclude Nulls on Avg

[anushree1]

HI ,

I have an expression like avg(aggr(Sum({<>}),Dim1,Dim2)) and i happen to see that avg is calculated including nulls in this case .

For eg if have Years and Div as Dim2 and Dim1

say Div1 10 has data for all 10 Years and Div2 has data for 9 Years my avg has sum/ count viz., 9 But its taking sum/count(10)

[anushree1]

Sample Data attached

Here For C Avg should be 90 but the value appearing is 15

[ogautier62]

Hi,

may be you can try this in your set analysis :

Sum({< Value=P({*}  >})  to exclude null values

[sunny_talwar]

Or this

Avg({<Value = {"*"}>}aggr(sum(Value),Div,Year))