Complex calculations on a table's results

[gaspafter26]

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?

 

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

 

[gaspafter26]

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

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.