Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

conseguir com avg e resident

View solution in original post

5 Replies
Duck
Contributor II
Contributor II

 

 please someone help me

Duck
Contributor II
Contributor II

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

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

conseguir com avg e resident

View solution in original post