# Thread: Complex calculations on a table's results

1. ## 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. 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.

3. Thanks very much for this. You have pointed me where I need to go, much appreciated.

Originally Posted by p45cal
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.

#### Posting Permissions

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