Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2011-01-09 05:34 PM

Exclude zero values from and avg calculation

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



3,099 Views
0 Likes
5 Replies
Not applicable
‎2012-02-16 10:49 AM
Author

Hi,

to exclude zero values you can try this

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

bye

Massikmo

1,493 Views
0 Likes
jagan
Luminary Alumni
Luminary Alumni
‎2012-02-16 10:59 AM

Hi,

Use the following expression

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

Regards,

jagan.

1,493 Views
0 Likes
Not applicable
‎2014-03-03 09:43 AM
Author

In response to jagan

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_idTraysTxn_DateState_Code
10007344NBG18/09/2013 12:33CMP
10007344NBG18/09/2013 12:33RDY
10007344NSS18/09/2013 16:23RDY
10007344NSS19/09/2013 09:05RUN
10007344NSS19/09/2013 09:07CMP
10007344CJA19/09/2013 09:07RDY
10007344CJA19/09/2013 09:11CMP
10007344NCO19/09/2013 09:11RDY
10007344NCO19/09/2013 09:20RUN
10007344NCO19/09/2013 09:20CMP
10007344NFU25/09/2013 08:28RDY
10007344NFU25/09/2013 08:28RUN
10007344NFU25/09/2013 08:28CMP
10007344NCH25/09/2013 08:28RDY
10007344NCH25/09/2013 11:56RUN
10007344NCH25/09/2013 12:02CMP
10007344NBO25/09/2013 12:02RDY
10007344NBO25/09/2013 12:03RUN
10007344NBO25/09/2013 12:07CMP
10007344NCA25/09/2013 12:07CMP
10007344NCA25/09/2013 12:07RDY

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

Application_idTrayTxn_DateState_Code
10007616NBG12/09/2013 10:42:29CMP
10007616NBG12/09/2013 10:42:29RDY
10007616NSA12/09/2013 10:42:29RDY
10007616NSA12/09/2013 11:25:55RUN
10007616NSA12/09/2013 11:26:33CMP
10007616CJA12/09/2013 11:26:33RDY
10007616CJA12/09/2013 11:27:24CMP
10007616NCO12/09/2013 11:27:24RDY
10007616NCO12/09/2013 12:56:32RUN
10007616NCO12/09/2013 13:00:24CMP
10007616NSA12/09/2013 13:00:24RDY
10007616NSA13/09/2013 16:20:38RUN
10007616NSA13/09/2013 16:22:08CMP
10007616CJA13/09/2013 16:22:08RDY
10007616CJA13/09/2013 16:25:47CMP
10007616NCO13/09/2013 16:25:47RDY
10007616NCO13/09/2013 16:50:57RUN
10007616NCO13/09/2013 16:51:47RDY
10007616NCO16/09/2013 10:42:22RUN
10007616NCO16/09/2013 10:43:00CMP
10007616NFU16/09/2013 10:43:00SUS
10007616NFU24/09/2013 08:31:02RDY
10007616NFU24/09/2013 08:31:02RUN
10007616NFU24/09/2013 08:31:05CMP
10007616NCH24/09/2013 08:31:05RDY
10007616NCH24/09/2013 12:13:58RUN
10007616NCH24/09/2013 12:15:25CMP
10007616NBO24/09/2013 12:15:25RDY
10007616NBO24/09/2013 12:15:26RUN
10007616NBO24/09/2013 12:16:01RDY
10007616NBO24/09/2013 12:16:04RUN
10007616NBO24/09/2013 12:16:05EXP
10007616NCH24/09/2013 12:16:05RDY
10007616NCH24/09/2013 13:25:26RUN
10007616NCH24/09/2013 13:26:20CMP
10007616NRD24/09/2013 13:26:20RDY
10007616NRD24/09/2013 15:30:05RUN
10007616NRD24/09/2013 15:30:07EXP
10007616NCH24/09/2013 15:30:07RDY
10007616NCH24/09/2013 15:30:08RUN
10007616NCH24/09/2013 15:31:42CMP
10007616NBO24/09/2013 15:31:42RDY
10007616NBO24/09/2013 15:31:43RUN
10007616NBO24/09/2013 15:37:25RDY
10007616NBO24/09/2013 15:37:27RUN
10007616NBO24/09/2013 15:37:32EXP
10007616NSA24/09/2013 15:37:32RDY
10007616NSA24/09/2013 15:39:51RUN
10007616NSA24/09/2013 15:41:37CMP
10007616CJA24/09/2013 15:41:37RDY
10007616CJA24/09/2013 15:43:42CMP
10007616NCO24/09/2013 15:43:42RDY
10007616NCO24/09/2013 15:52:46RUN
10007616NCO24/09/2013 15:53:44CMP
10007616NFU24/09/2013 15:53:44SUS
10007616NFU24/09/2013 15:53:47RDY
10007616NFU24/09/2013 15:53:47RUN
10007616NFU24/09/2013 15:54:06CMP
10007616NCH24/09/2013 15:54:06RDY
10007616NCH24/09/2013 15:54:08RUN
10007616NCH24/09/2013 15:54:12CMP
10007616NBO24/09/2013 15:54:12RDY
10007616NBO24/09/2013 15:54:14RUN
10007616NBO24/09/2013 15:55:56CMP
10007616NCA24/09/2013 15:55:56CMP
10007616NCA24/09/2013 15:55:56RDY
1,493 Views
0 Likes
Not applicable
‎2014-03-03 10:37 AM
Author

In response to

is it the right place for this question ?

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

Fabrice

1,493 Views
0 Likes
Not applicable
‎2014-03-03 10:40 AM
Author

In response to

Trying for past few weeks but could not get

1,493 Views
0 Likes