Posts by Zaheer

  • Pivot Table Filtering by Subtotal

    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.

  • Pivot Table Show pages menu item

    Re: Pivot Table Show pages menu item


    The Show Pages function is on the Pivot table toolbar, but not by default.


    Display the toolbar[View > Toolbars > Pivot Table], then click the small arrow on the end of the pivot table toolbar > add / remove buttons> Pivot Table > Show Pages

  • Pivot Table Filtering by Subtotal

    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

  • Pivot - Advance Filter

    Re: Pivot - Advance Filter


    I had this same issue with my pivot tables - I'm using CSV files as the datasource.If you want to be updating your Table on a regular basis I see 2 ways:


    1st method
    I wanted to group large accounts, and everyone else. Since I have a table that contains Customer + Region linked linked in, I added a column onto that that had: For top 5 customers write their names; for others write others.


    I then used that field in my pivot table rather than the standard CustomerID field. Abit long winded, but since I run macros every time I suck in new data to my pivot table, it isn't too bad.


    2nd Method
    Select every Project apart from your top 3 projects within the pivot table, right click and select Group. It will place them in group 1: You can then rename that to Others.


    Remove your main project field, and you will be left with your top 3 projects and group 1.


    The problem I see with this method is you have to regroup every time new data [specifically new projects] are added to the pivot table, but I don't know of any way other than these two.

  • 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.