topic Re: Average with condition in QlikView

Average with condition

Duck — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Average with condition

Duck — Thu, 01 Aug 2019 14:47:16 GMT

please someone help me

Re: Average with condition

Duck — Mon, 05 Aug 2019 12:26:53 GMT

no Solution

Re: Average with condition

BlondeThursday — Mon, 05 Aug 2019 13:27:58 GMT

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.

Re: Average with condition

Duck — Mon, 05 Aug 2019 14:47:23 GMT

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.

Re: Average with condition

Duck — Mon, 16 Sep 2019 13:28:45 GMT

conseguir com avg e resident