Skip to Content
0
Former Member
Feb 10, 2011 at 09:26 PM

Count from first non-null value in list

423 Views

Iu2019m trying to calculate an average quantity for a range of months entered at the prompt for a crosstab report, however I only want to count beginning with the first month that I had a quantity that was not null. I want to include subsequent null quantity columns in the calculation. How would I go about this?

Example:

Months 01/2010 02/2010 03/2010 04/2010 05/2010 06/2010

Quantities 0 0 500 0 750 475

The expected result is (5000750+475) /4 or 431.25.

How do I achieve this if I have no way of knowing which month was the first non-null month in the range entered by the user? Can this be done in the query?

I really appreciate your help.

Nancy