Exclude zero values from and avg calculation

Sun, 09 Jan 2011 22:34:45 GMT

I have the following formula which is diong the avg for three values. If value -3 is zero and -4 and -5 are greater than zero the avg calcualtion will include the zero value in the avg. Is it possible to exclude the zero value from the avg.

=(if(sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-3),'YYYYMM'))'}>}[MemMonths])=0,sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-3),'YYYYMM'))'}>}[MemMonths_HOSP]),sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-3),'YYYYMM'))'}>}[MemMonths])) +

if(sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-4),'YYYYMM'))'}>}[MemMonths])=0,sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-4),'YYYYMM'))'}>}[MemMonths_HOSP]),sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-4),'YYYYMM'))'}>}[MemMonths])) +

if(sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-5),'YYYYMM'))'}>}[MemMonths])=0,sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-5),'YYYYMM'))'}>}[MemMonths_HOSP]),sum({$<DateofService={'$(=date(AddMonths(Max({$}AnchorDate),-5),'YYYYMM'))'}>}[MemMonths])) ) / 3

Thanks,

Exclude zero values from and avg calculation

Thu, 16 Feb 2012 15:49:43 GMT

Hi,

to exclude zero values you can try this

=Avg({< [Nr Teste] = {"<>0"} >} [Nr Teste])

bye

Massikmo

Exclude zero values from and avg calculation

jagan — Thu, 16 Feb 2012 15:59:32 GMT

Hi,

Use the following expression

=Avg({< [Nr Teste] = {'>0'} >} [Nr Teste])

Regards,

jagan.

Re: Exclude zero values from and avg calculation

Mon, 03 Mar 2014 14:43:12 GMT

Hi Jagan,

The application can move in different trays in the workflow. Each tray has different State_Codes. e.g. Tray = NSS has State_code RDY, RUN & CMP in the following example. But each tray would have RDY (shows application came in a tray) and CMP (application completed in a tray). How can I get that how many working hours each application stayed in each tray. Working days are Monday to Friday and working hours are 9:00-17:00. There is also a problem that the application can go in each tray once or more than once as two examples below.

Once example: In this example application moved in each tray only once.

Application_id	Trays	Txn_Date	State_Code
10007344	NBG	18/09/2013 12:33	CMP
10007344	NBG	18/09/2013 12:33	RDY
10007344	NSS	18/09/2013 16:23	RDY
10007344	NSS	19/09/2013 09:05	RUN
10007344	NSS	19/09/2013 09:07	CMP
10007344	CJA	19/09/2013 09:07	RDY
10007344	CJA	19/09/2013 09:11	CMP
10007344	NCO	19/09/2013 09:11	RDY
10007344	NCO	19/09/2013 09:20	RUN
10007344	NCO	19/09/2013 09:20	CMP
10007344	NFU	25/09/2013 08:28	RDY
10007344	NFU	25/09/2013 08:28	RUN
10007344	NFU	25/09/2013 08:28	CMP
10007344	NCH	25/09/2013 08:28	RDY
10007344	NCH	25/09/2013 11:56	RUN
10007344	NCH	25/09/2013 12:02	CMP
10007344	NBO	25/09/2013 12:02	RDY
10007344	NBO	25/09/2013 12:03	RUN
10007344	NBO	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	RDY

More than Once example: Application moved into NSA twice, CJA three times etc.

Application_id	Tray	Txn_Date	State_Code
10007616	NBG	12/09/2013 10:42:29	CMP
10007616	NBG	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 11:25:55	RUN
10007616	NSA	12/09/2013 11:26:33	CMP
10007616	CJA	12/09/2013 11:26:33	RDY
10007616	CJA	12/09/2013 11:27:24	CMP
10007616	NCO	12/09/2013 11:27:24	RDY
10007616	NCO	12/09/2013 12:56:32	RUN
10007616	NCO	12/09/2013 13:00:24	CMP
10007616	NSA	12/09/2013 13:00:24	RDY
10007616	NSA	13/09/2013 16:20:38	RUN
10007616	NSA	13/09/2013 16:22:08	CMP
10007616	CJA	13/09/2013 16:22:08	RDY
10007616	CJA	13/09/2013 16:25:47	CMP
10007616	NCO	13/09/2013 16:25:47	RDY
10007616	NCO	13/09/2013 16:50:57	RUN
10007616	NCO	13/09/2013 16:51:47	RDY
10007616	NCO	16/09/2013 10:42:22	RUN
10007616	NCO	16/09/2013 10:43:00	CMP
10007616	NFU	16/09/2013 10:43:00	SUS
10007616	NFU	24/09/2013 08:31:02	RDY
10007616	NFU	24/09/2013 08:31:02	RUN
10007616	NFU	24/09/2013 08:31:05	CMP
10007616	NCH	24/09/2013 08:31:05	RDY
10007616	NCH	24/09/2013 12:13:58	RUN
10007616	NCH	24/09/2013 12:15:25	CMP
10007616	NBO	24/09/2013 12:15:25	RDY
10007616	NBO	24/09/2013 12:15:26	RUN
10007616	NBO	24/09/2013 12:16:01	RDY
10007616	NBO	24/09/2013 12:16:04	RUN
10007616	NBO	24/09/2013 12:16:05	EXP
10007616	NCH	24/09/2013 12:16:05	RDY
10007616	NCH	24/09/2013 13:25:26	RUN
10007616	NCH	24/09/2013 13:26:20	CMP
10007616	NRD	24/09/2013 13:26:20	RDY
10007616	NRD	24/09/2013 15:30:05	RUN
10007616	NRD	24/09/2013 15:30:07	EXP
10007616	NCH	24/09/2013 15:30:07	RDY
10007616	NCH	24/09/2013 15:30:08	RUN
10007616	NCH	24/09/2013 15:31:42	CMP
10007616	NBO	24/09/2013 15:31:42	RDY
10007616	NBO	24/09/2013 15:31:43	RUN
10007616	NBO	24/09/2013 15:37:25	RDY
10007616	NBO	24/09/2013 15:37:27	RUN
10007616	NBO	24/09/2013 15:37:32	EXP
10007616	NSA	24/09/2013 15:37:32	RDY
10007616	NSA	24/09/2013 15:39:51	RUN
10007616	NSA	24/09/2013 15:41:37	CMP
10007616	CJA	24/09/2013 15:41:37	RDY
10007616	CJA	24/09/2013 15:43:42	CMP
10007616	NCO	24/09/2013 15:43:42	RDY
10007616	NCO	24/09/2013 15:52:46	RUN
10007616	NCO	24/09/2013 15:53:44	CMP
10007616	NFU	24/09/2013 15:53:44	SUS
10007616	NFU	24/09/2013 15:53:47	RDY
10007616	NFU	24/09/2013 15:53:47	RUN
10007616	NFU	24/09/2013 15:54:06	CMP
10007616	NCH	24/09/2013 15:54:06	RDY
10007616	NCH	24/09/2013 15:54:08	RUN
10007616	NCH	24/09/2013 15:54:12	CMP
10007616	NBO	24/09/2013 15:54:12	RDY
10007616	NBO	24/09/2013 15:54:14	RUN
10007616	NBO	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	RDY

Re: Exclude zero values from and avg calculation

Mon, 03 Mar 2014 15:37:27 GMT

is it the right place for this question ?

open your own thread: you will get appropriate answer, I am sure of it

Fabrice

Re: Exclude zero values from and avg calculation

Mon, 03 Mar 2014 15:40:13 GMT

Trying for past few weeks but could not get

topic Exclude zero values from and avg calculation in QlikView

Exclude zero values from and avg calculation

Exclude zero values from and avg calculation

Exclude zero values from and avg calculation

Re: Exclude zero values from and avg calculation

Re: Exclude zero values from and avg calculation

Re: Exclude zero values from and avg calculation