Exclude zero values from and avg calculation

[#1]

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,

K

[#2]

Hi,

to exclude zero values you can try this

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

bye

Massikmo

[jagan]

Hi,

Use the following expression

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

Regards,

jagan.

[#4]

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

[#5]

is it the right place for this question ?

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

Fabrice

[#6]

Trying for past few weeks but could not get