disregaring zero or null values in computing average

[chan103148]

good day,

need help

this is my data:

DATE	Traffic
1/1/2020	329
1/2/2020	346
1/3/2020	324
1/4/2020	363
1/5/2020	347
1/6/2020	239
1/7/2020	209
1/8/2020	201
1/9/2020	213
1/10/2020	254
1/11/2020	399
1/12/2020	395
1/13/2020	159
1/14/2020	222
1/15/2020	0
1/16/2020	0
1/17/2020	0
1/18/2020	0
1/19/2020	353
1/20/2020	158
1/21/2020	201
1/22/2020	218
1/23/2020	183
1/24/2020	244
1/25/2020	303
1/26/2020	348

Qlik sense is computing average traffic with those dates with zero count which gives me 231 average traffic, I want to disregard those dates without a traffic count to get more reliable average traffic.

here is my script:

avg(Aggr(Sum({$<[DATE.autoCalendar.YearMonth]={"2020-Jan"}>}[Traffic In]),[DATE],[$STORE]))

thank you

[Mauritz_SA · Accepted solution]

Hi there

You can do this by adding that condition in your set analysis:

avg(Aggr(Sum({$<[DATE.autoCalendar.YearMonth]={"2020-Jan"},Traffic={">0"}>}[Traffic In]),[DATE],[$STORE]))

Hope this helps.

Regards,

Mauritz