topic Re: average count in QlikView

average count

realpixel — Fri, 15 Nov 2013 15:47:42 GMT

Hello,

I use the following expression to have the average of the column Ecart Datr/Datfac (DELTA1)

avg(num (Interval(DTFAC1-DFAC1)))

The result give me 14,27 if I extract in Excel the total of expression give me 2712 and the number the line is 187 (without the blank field)

2712/187 = 14,5026 is not the same 14,27

Someone have an idea, maybe because the column Ecart Datr/Datfac (DELTA1) have a field blank?

Re: average count

swuehl — Fri, 15 Nov 2013 15:56:29 GMT

Yes, I assume the difference is because of NULLs in your field.

Have you tried summing up your field and dividing by the count of an appropriate field (not containing NULLs)?

=sum( DELTA1) / count( KEYFIELD)

Re: average count

realpixel — Fri, 15 Nov 2013 17:17:10 GMT

Thank you Swuehl for your reply

Yes I want to try to sum my [Ecart Datr/Datfac (DELTA1)] and divide by the count of an appropriate field (not containing NULL value)

I tried someting lik that =sum( [Ecart Datr/Datfac (DELTA1)]) / count( KEYFIELD) but I don't know what kind of value in KEYFIELD?

Re: average count

swuehl — Fri, 15 Nov 2013 17:28:40 GMT

That's hard to answer without knowing your data.

I assume your field you want to average is located in a table, and it's not the only field in the table.

Probably one of the other fields does not have NULLs, maybe your key field or some other appropriate field.

You'll need to describe your setting a bit closer if you want someone to help you identifying the appropriate field.

Re: average count

realpixel — Fri, 15 Nov 2013 18:20:19 GMT

This a part of my table

I need to have the average for the expression under

Ecart Datr/Datfac (DELTA1) = avg(num (Interval(DTFAC1-DFAC1)))

Ecart Dabord./Datr (DELTA2) = num(Interval(DBOR1-DTFAC1))

Ecart Da. Arcli/Dabord (DELTA3) = avg(num(Interval(DACR1-DBOR1)))

TYPE FACTURE	METIERS	DFAC1	DTFAC1	DBOR1	DACR1	Ecart Datr/Datfac (DELTA1)	Ecart Dabord./Datr (DELTA2)	Ecart Da. Arcli/Dabord (DELTA3)
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	16/09/2013	3	0	0
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	16/09/2013	3	0	0
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	TRANSIT	13/09/2013	16/09/2013	16/09/2013	17/09/2013	3	0	1
Facture	LOGISTIQUE	23/09/2013	30/09/2013	30/09/2013	30/09/2013	7	0	0
Avoir	TRANSIT	11/02/2013	-	20/02/2013	20/03/2013	-	-	28
Facture	TRANSIT	30/01/2013	-	15/03/2013	20/03/2013	-	-	5
Avoir	TRANSIT	10/05/2013	-	13/05/2013	14/05/2013	-	-	1
Facture	TRANSIT	06/02/2013	-	11/02/2013	12/02/2013	-	-	1
Facture	TRANSIT	06/02/2013	-	11/02/2013	12/02/2013	-	-	1
Facture	TRANSIT	07/02/2013	-	11/02/2013	12/02/2013	-	-	1
Facture	TRANSIT	28/01/2013	-	12/02/2013	13/02/2013	-	-	1
Facture	TRANSIT	29/03/2013	-	02/04/2013	03/04/2013	-	-	1
Facture	TRANSIT	29/03/2013	-	02/04/2013	03/04/2013	-	-	1
Facture	TRANSIT	30/03/2013	-	02/04/2013	03/04/2013	-	-	1
Facture	TRANSIT	26/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	29/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	29/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	29/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	29/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	29/04/2013	-	30/04/2013	02/05/2013	-	-	2
Facture	TRANSIT	27/06/2013	-	27/06/2013	28/06/2013	-	-	1
Facture	TRANSIT	27/06/2013	-	27/06/2013	28/06/2013	-	-	1
Facture	TRANSIT	30/08/2013	-	02/09/2013	03/09/2013	-	-	1
Facture	TRANSIT	30/08/2013	-	02/09/2013	03/09/2013	-	-	1
Facture	TRANSIT	29/01/2013	-	13/02/2013	18/02/2013	-	-	5
Facture	TRANSIT	08/02/2013	-	13/02/2013	18/02/2013	-	-	5
Avoir	TRANSIT	11/02/2013	-	13/02/2013	18/02/2013	-	-	5
Facture	TRANSIT	07/01/2013	-	16/01/2013	05/03/2013	-	-	48