Ugly Problem

[Alan Graybosch]

Here's what I've got...

Need to summarize data by person (count, median, 95th percentile) by
month for the last 12 months. I've got a query that returns ~6000 rows
x 10 columns and I did a pivot table to get all the unique person names
(~40). I then added columns for the months and entered array formulas
(for the count, median, 95th percentile) comparing the person's name
and month - pretty cool and it works to a degree...

However, in order to summarize variations of the same data I end up
with multiple array formula tables - each 40 rows by 12 cols - and
performance is terrible after the 4th table. It's been recalculating
for over 90 min. I recently implemented dynamic named ranges and that
seemed to help a bit.

In researching I understand that one of the drawbacks to array formulas
can be slow performance and the recommended solution is to use database
formulas. Problem is how do I best organize the criteria when I have 40
names to summarize over 12 months - *wouldn't I need 480 criteria
combinations*? Or is there a simpler way to handle those criteria
combinations. Regardless, as a new person is added, I lose the dynamic
nature of the array formulas. Not to mention there is no DPERCENTILE
function.

Alan

 

[Guest]

Hi,

As you have started with a pivot table, you may continue using it for the
calculations. There are some that are standard (like count), and others that
may be done either with the Field options or calculated fields.
For the ones that couldn't be made that way, you may use database functions
directly with the pivot table, as the underlying data is there, or use the
GETPIVOTDATA and craft the formulas based on that.

Hope this helps,
Miguel.