Only showing lines with one or more empties

I noticed this question on the BOB forum -- and I thought I'd have a stab at it. Here goes:

Consider the following table :

 

Some months have data for all years, some only have data for one or two of the years. The person on the BOB forum asked, if it's possible to only show those where one or more of the years does not have data for one of the months.

Our first step is, we need to determine, just how many years there are.

=Count([Year] ) in Report - we call this calculation TotalNYears

Next, we need to determine, the number of years present for each of the months.

=Count([Year] )in ([Month]) - we call this one NYears

Next, we need to subtract those two.

=[TotalNYears] - [NYears] and we call that one : DifferenceNyears

and last but not least,

we filter that table for [DifferenceNYears]>0

 

Nice, isn't it ;) If you set the filter to : differenceNYears = 0 then, you only have those where each year has data. In fact, I remember writing a blogpost a few years back where I showed that last thing, and how to do it at the query level. It involved combining multiple queries.. this method is MUCH faster.

Peter