Replace Zero with Average from User

[LeHaoNguyen]

HI Qlik Community,

i try to create a Table in Qlik Sense to show the time difference between timestamps from each user. in my load i replaced all timestampts over 7200seconds to zero. Now in Qlik Sense i got like following Table:

Product User Time difference

Product	User	Timestamp	Timediff
0001	9001	11:52:00	39
0002	9001	11:52:39	60
0003	9001	11:53:39	0
0004	9001	17:59:00	0
0001	9002	08:00:00	600
0002	9002	08:10:00	120
0003	9002	08:12:00	180
0004	9002	08:15:00	0
0005	9002	22:32:00	60
0006	9002	22:33:00	0

thats the table i can create right now in qlik sense but i want to replace the zeros in timediff with the average of the user so the 2 zeros from user 9001 gets replaced with (39+60)/2=49,5 and the two zeros from user 9002 gets replaced with (600+120+180+60)/4=240

i tried to make a new column with this expression but it doesnt seem to work:

if(timediff=0,Aggr(Avg ({<timediff={">0"}>}timediff), user),timediff)

Anybody got an idea why my solution doesnt work?

Greetings

Le-Hao Nguyen

[sunny_talwar · Accepted solution]

Try this

If(timediff = 0, Avg(TOTAL <User> {<timediff={">0"}>} timediff), timediff)

or

If(timediff = 0, Aggr(NODISTINCT Avg({<timediff={">0"}>} timediff), User), timediff)