Pivot Tables filtering data
 

Using Excel, I have query(using an ODBC connection) of all customer order
detail over a two year time period. The detail for each customer is
different in that some customer have multiple orders, and the amount of
individual sales parts they order may be 1 or 100. My query gathers detail
by order line item. We were able to provide management with comparative
sales data.

Now that we have shown this report to management, they want three things
that I'm unsure how to provide. 1) Customers that are new in the current
year2) customers that had orders in both years, 3)customers that ordered in
the prior year that have no cy orders.

Using the pivot table funtion, is there any way to filter data, so that if
both years are 0, then show those customers? To show if the difference
between the data is positive or negative, only those customers, respectively?


Remember that the line item detail in my query only shows one sales part,
for one year's activity per row.

Thank you.

I've ran into this type of problem before.

The best I've managed to accomplish is to display
- the years in down the left columns
- the customer # along the top row
- and a count of the records in the data area
By manually viewing this "matrix" of information you can visially see the gaps and years that have orders. By using the drill down on the subtotals you can inspect the records in greater detail. Granted, this method wouldn't work for a large setup of unique customers but should get you through the night on smaller data sets.

I'd be interested if someone else has something else to offer. MS needs to add more functionality to PTs