Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
conseguir com avg e resident
please someone help me
no Solution
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.
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.
conseguir com avg e resident