# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
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
Contributor II

conseguir com avg e resident

5 Replies
Contributor II
` `

Contributor II

no Solution

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.

Contributor II

sorry, correcting the result:

 date line hour pass average 02/03/19 202 05:20 86 130 02/03/19 202 06:03 244 274 02/03/19 312 06:10 104 31 02/03/19 312 07:40 97 -

In excel I solve like this:

 A B C D G H I J K L 01/03/2019 202 05:10:00 148 date line hour pass average 00:30 02/03/2019 202 05:20:00 86 02/03/19 202 05:20 86 130 =MÉDIASES(\$D:\$D;\$A:\$A;\$G2-1;\$B:\$B;\$H2;\$C:\$C;">="&\$I2-\$L\$1;\$C:\$C;"<"&\$I2+\$L\$1) 01/03/2019 202 05:30:00 112 02/03/19 202 06:03 244 274 03/03/2019 202 05:41:00 184 02/03/19 312 06:10 104 31 01/03/2019 202 05:52:00 274 02/03/19 312 07:40 97 - 02/03/2019 202 06:03:00 244 01/03/2019 312 05:40:00 31 02/03/2019 312 06:10:00 104 03/03/2019 312 06:40:00 93 03/03/2019 312 07:10:00 153 02/03/2019 312 07:40:00 97 01/03/2019 312 08:20:00 66

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

Contributor II

conseguir com avg e resident

Tags