average by considering empty field as zero

ahmedwank — Wed, 25 Nov 2020 16:16:04 GMT

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 :

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!

Re: average by considering empty field as zero

Gysbert_Wassenaar — Sun, 10 Mar 2019 07:21:03 GMT

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