Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Duck
Contributor II
Contributor II

Average with condition

Please someone help me, I'm a beginner and I don't know what to do.

date | line | hour | pass
01/03/19 | 202 | 05:10:00 | 148
02/03/19 | 202 | 05:20:00 | 86
01/03/19 | 202 | 05:30:00 | 112
03/03/19 | 202 | 05:41:00 | 184
01/03/19 | 202 | 05:52:00 | 274
02/03/19 | 202 | 06:03:00 | 244
01/03/19 | 312 | 05:40:00 | 31
02/03/19 | 312 | 06:10:00 | 104
03/03/19 | 312 | 06:40:00 | 93
03/03/19 | 312 | 07:10:00 | 153
02/03/19 | 312 | 07:40:00 | 97
01/03/19 | 312 | 08:20:00 | 66

calculate for the selected date the average of the previous day.
condition average: previous date, same line and time> =(time-00:30:00) time<(time+00:30:00)

Result

Selected 02/03/19

date | line | hour | pass | average
02/03/19 | 202 | 05:20:00 | 86 | 178
02/03/19 | 202 | 06:03:00 | 244 | 193
02/03/19 | 312 | 06:10:00 | 104 | 31
02/03/19 | 312 | 07:40:00 | 97 | 66

1 Solution

Accepted Solutions
Duck
Contributor II
Contributor II
Author

conseguir com avg e resident

View solution in original post

5 Replies
Duck
Contributor II
Contributor II
Author

 

 please someone help me

Duck
Contributor II
Contributor II
Author

no Solution

BlondeThursday
Contributor III
Contributor III

I don't see how you get your expected results with the parameters you set.

I can give you the results in a chart if you average all pass from the previous date and line, but 5:52 (on the 1st) is more than 30 mins later than 5:20 (on the second) so shouldn't be included in your average.

But...

=Avg({<date={"=date-1"}>}pass)

will give you the average for each line on the previous day if you set dimensions of date and line.

Duck
Contributor II
Contributor II
Author

sorry, correcting the result:

datelinehourpassaverage
02/03/1920205:2086130
02/03/1920206:03244274
02/03/1931206:1010431
02/03/1931207:4097-

 

In excel I solve like this:

ABCD  GHIJKL
01/03/201920205:10:00148  datelinehourpassaverage00:30
02/03/201920205:20:0086  02/03/1920205:2086130=MÉDIASES($D:$D;$A:$A;$G2-1;$B:$B;$H2;$C:$C;">="&$I2-$L$1;$C:$C;"<"&$I2+$L$1)
01/03/201920205:30:00112  02/03/1920206:03244274 
03/03/201920205:41:00184  02/03/1931206:1010431 
01/03/201920205:52:00274  02/03/1931207:4097- 
02/03/201920206:03:00244        
01/03/201931205:40:0031        
02/03/201931206:10:00104        
03/03/201931206:40:0093        
03/03/201931207:10:00153        
02/03/201931207:40:0097        
01/03/201931208:20:0066        


I can't do this in qlik, I need help.

Duck
Contributor II
Contributor II
Author

conseguir com avg e resident