average by considering empty field as zero

[ahmedwank]

hi all

i am trying to calculate a basic average by date but i need to consider the empty field as zero i the average.

let's imagine we have the distinct date from 01/01/2019 --> 10/01/2019

exemple :

date	ref	qty ordered
01/01/2019	A	10
02/01/2019	A	20

The average for the ref A should be (10+20)/10 (= distinct dates) = 3

but when i used avg(aggr(sum(Qty),ref,Date)) i only see an average without considering the date where we have 0 qty 

thanks a lot in advance!

[Gysbert_Wassenaar · Accepted solution]

Try: sum(Qty)/count(distinct Date)