Results 1 to 3 of 3

Thread: Complex calculations on a table's results

  1. #1
    Neophyte gaspafter26's Avatar
    Join Date
    Oct 2021
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft 365

    Complex calculations on a table's results



    Register for a FREE account, and/
    or Log in to avoid these ads!

    The underlying data looks like this
    account name month sales
    1090 Hertz Jan-21 13
    1090 Hertz Feb-21 7
    1090 Hertz Mar-21 8
    1090 Hertz Apr-21 2
    1121 Avis Jan-21 4
    1121 Avis Feb-21 7
    1121 Avis Mar-21 9
    1121 Avis Apr-21 1
    1311 Budget Jan-21 7
    1311 Budget Feb-21 7
    1311 Budget Mar-21 3
    1311 Budget Apr-21 5
    1002 Alamo Jan-21 3
    1002 Alamo Feb-21 2
    1002 Alamo Mar-21 2
    1002 Alamo Apr-21 0

    The pivot table looks like this

    Sum of sales Months
    account name Jan Feb Mar Apr Grand Total
    1002 Alamo 3 2 2 0 7
    1090 Hertz 13 7 8 2 30
    1121 Avis 4 7 9 1 21
    1311 Budget 7 7 3 5 22
    Grand Total 27 23 22 8 80

    My objective is to do a more complex calculation - I want to show only those records where at least 2 of the last 3 months, the sales are 5 or less.

    I tried to add a calculated item, but the error I get says too many records, and I have not been able to solve that

    I've tried endless hacks to work outside the pivot table, but none work.

    Can someone point me in the direction of a solution?

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,024
    Articles
    0
    Excel Version
    365
    In the attached is your pivot at cell G2, then at cell G18 there's a pivot based on a query of the table at cell A1.
    The query does the following:
    • Grabs the table at cell A1
    • Works out a cutoff date by subtracting 3 months from the latest date in the table
    • Filters the table for only dates after that cutoff (the last 3 months)
    • Filters the table for sales <=5
    • Groups using account and name counting how many rows in each group
    • Filters that count for 2 or more which leaves us with a small table of accounts and names
    • which is merged (joined) with the original table to retrieve all the months' data for just those accounts/names
    • which is then pivoted on the sheet.
    Attached Files Attached Files
    Last edited by p45cal; 2021-10-17 at 02:43 PM.

  3. #3
    Neophyte gaspafter26's Avatar
    Join Date
    Oct 2021
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft 365
    Thanks very much for this. You have pointed me where I need to go, much appreciated.


    Quote Originally Posted by p45cal View Post
    In the attached is your pivot at cell G2, then at cell G18 there's a pivot based on a query of the table at cell A1.
    The query does the following:
    • Grabs the table at cell A1
    • Works out a cutoff date by subtracting 3 months from the latest date in the table
    • Filters the table for only dates after that cutoff (the last 3 months)
    • Filters the table for sales <=5
    • Groups using account and name counting how many rows in each group
    • Filters that count for 2 or more which leaves us with a small table of accounts and names
    • which is merged (joined) with the original table to retrieve all the months' data for just those accounts/names
    • which is then pivoted on the sheet.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •