Matrix Reports Filter

I am trying to have a matrix report filter on records that had sales in a prior month but no sales in the current month. Basically looking for accounts we “lost”

For example:

Name Invoice Date Quantity

Joe 4/1/2007 7

John 5/1/2007 13

John 4/1/2007 7

Tim 6/12/2007 6

The matrix report pulls back

Name Month

April May June

Joe 7

Tim 7 13

John 6

I am trying to return only names that had quantity in May and don't in June.

I would like it only to return Tim’s record because we lost his business in June.

Any ideas?

I think in your example you switched Tim and John between the query results and matrix.

Anyway, the best place to do this is in your query. I suspect you'll be running this report based on a "Month" parameter.

So in pseudo code you would write your query like

Code Snippet

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month - 1 month

AND account NOT IN

(

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month

)