Pivot Table Filtering by Subtotal

    I am using a pivot table to represent a database of ordering information. Since the source information is too large to fit on a spreadsheet, I've created several CSV Files to hold the data, and am linking them together using Microsoft Query.


    Each record has CustomerID, Order No, Line No, Product, Date & Quantity. A Customer will place many orders, and within each order there may be several lines.


    What I want to be able to do is Filter the pivot table so that I only see those Customers that have an order quantity above e.g. 20,000. If I just displayed CustomerID Vs Quantity, then I'd be wanting to filter on the data area.


    Since I'm using a huge CSV File, I've not found a way to filter the data easily before importing it into the pivot table.


    I have spend a fair bit of time trying to figure out a way to achieve this, but so far not had any success. Any suggestions would be appreciated.

    Re: Pivot Table Filtering by Subtotal


    Quote from Zaheer

    Since the source information is too large to fit on a spreadsheet, I've created several CSV Files to hold the data, and am linking them together using Microsoft Query.


    Why?


    Can you not just query the database for the subsets of data you wish to report ?


    I can't quite get my head around the need for CSV files....

    Re: Pivot Table Filtering by Subtotal


    If you are using MS query could you not just add a field that indicates if a customer has more than 20,000 or not and use it as a page field in the pivot?

    Boo!:yikes:

    Re: Pivot Table Filtering by Subtotal


    Will Rylie: Regarding databases: It isn't easy or very clear how to query the database I'm extracted the data from to give this result set; its fairly old and my knowledge of the system is somewhat limited.


    Norie: How would I add a field to check whether a customer has more than 20 000 records in query? I've tried to filter on the sum of quantity, but the criteria it generates checks the figure I give 'per order per line', rather than the total figure for an account.


    Thanks

    Re: Pivot Table Filtering by Subtotal


    Quote from Zaheer

    its fairly old and my knowledge of the system is somewhat limited


    What is it..... ?


    I'm guessing here but if someone has the knowledge to create the csv files....then the queries might not be too difficult... ?

    Re: Pivot Table Filtering by Subtotal


    Its a progress database, sitting on our main application server which is running Linux. I can't get an ODBC link into the database because of the way in which it has been designed.


    What I do to create the CSV files is run a large report for the month [or half the month if theres too much data] and save it as a text file. Then I run an Excel macro that extracts the information I want and puts it into several columns. I then save the completed month as a CSV File.


    After that I just open up the CSV File in wordpad and Copy / Paste the information into the CSV "masterfile", and then use the Microsoft Text driver to bring the data into my Pivot table.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login