Average values by distinct date

Mon, 12 May 2014 22:41:18 GMT

Here is an example of my data set.

date	cases	type
4/26/2014
4/25/2014	0	92
4/25/2014	1	93
4/25/2014	1	162
4/24/2014	1	92
4/24/2014	3	93
4/23/2014	0	92
4/23/2014	0	93
4/22/2014	0	92
4/22/2014	0	93
4/22/2014	0	162
4/21/2014	0	92
4/21/2014	0	93
4/21/2014	0	97
4/21/2014	0	162
4/20/2014		92
4/20/2014		93
4/20/2014		97
4/20/2014		162

I would like to average the "cases" per day, regardless of "type". So, this would be an average of 1.2 (=6/5), since there were 2 on 4/24/2014, 4 on 4/24/2014, and 0 on 4/23/2014, 4/22/2014, and 4/21/2014. My average would not include 4/20/2014 or 4/26/2014 since the value for "cases" has a null on those days.

I tried avg(cases), but that gives me 0.375 (=6/16). It's counting all the numbers in the "cases" column as an instance.

I tried avg({<cases = {'<>0'}>} cases), but that gives me 1.5 (=6/4). It's counting all the non-zero numbers in the "cases" column as an instance, but I want it to count just the ones with distinct dates.

I tried avg(distinct {<cases = {'<>0'}>} cases), but that gives me 1.5 (=2/2). It's only taking '3' and '1' as the two instances to average.

I tried aggr, but I can't get that to even give me a number. I think I'm using it wrong.

Any ideas?

Re: Average values by distinct date

Mon, 12 May 2014 22:44:56 GMT

In case it matters, I want to aggregate this number to use it in a text box, not a chart or anything like that.

Re: Average values by distinct date

swuehl — Mon, 12 May 2014 22:59:22 GMT

What about

=sum(cases) / count({<cases = {"*"}>} distinct date)

Re: Average values by distinct date

Mon, 12 May 2014 23:04:22 GMT

That worked, thanks!

topic Re: Average values by distinct date in QlikView

Average values by distinct date

Re: Average values by distinct date

Re: Average values by distinct date

Re: Average values by distinct date