topic Re: Exclude Nulls on Avg in QlikView

Exclude Nulls on Avg

anushree1 — Thu, 24 May 2018 08:24:58 GMT

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 — Thu, 24 May 2018 08:37:06 GMT

Sample Data attached

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

ogautier62 — Thu, 24 May 2018 09:06:21 GMT

Hi,

may be you can try this in your set analysis :

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

sunny_talwar — Thu, 24 May 2018 12:28:46 GMT

Or this

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