0

I have a PivotTable that comes from the following table:

    +---------+---+-----+
    |    A    | B |  C  |
  +-+---------+---+-----+
  |1|   Date  |Id |Value|
  +-+---------+---+-----+
  |2|4/01/2013|1  |4    |
  +-+---------+---+-----+
  |3|4/01/2013|2  |5    |
  +-+---------+---+-----+
  |4|4/01/2013|1  |20   |
  +-+---------+---+-----+
  |5|4/02/2013|2  |20   |
  +-+---------+---+-----+
  |6|4/02/2013|1  |15   |
  +-+---------+---+-----+

And I want to aggregate first by Id and then by Date, using Max to aggregate by Id and then Sum to aggregate by Date. The resulting table would look like this:

    +---------+----------------+
    |    A    | B              |
  +-+---------+----------------+
  |1|   Date  |Sum(Max(Id,Date)|
  +-+---------+----------------+
  |2|4/01/2013|25              |
  +-+---------+----------------+
  |3|4/02/2013|35              |
  +-+---------+----------------+

The 25 above comes from getting the Max per Id per Date (Max(1, 4/01/2013) -> 20 and Max(2, 4/01/2013) -> 5, so the Sum of those Max is 25.

I can do the two levels of aggregation easily by adding the Date and Id columns into the Rows section of the PivotTable, but when choosing an aggregation function for Value, I can either choose Max, getting a Max of Max, or Sum, getting a Sum of Sum. That is, I cannot get a Sum of Max.

Do you know how to achieve this? Ideally, the solution would not be to compute a PivotTable and then copy from there or get a formula, because that would break easily if I want to dynamically change fields.

Thanks!

pnuts
  • 58,317
  • 11
  • 87
  • 139
aggFTW
  • 426
  • 3
  • 12

1 Answers1

0

This is how I would do it in SQL:

SELECT DATE, SUM(MAXED_VAL) as SummedMaxedVal
FROM (
     SELECT DATE, ID, MAX(VALUE) as MAXED_VAL
     FROM table
     GROUP BY DATE, ID
)
GROUP BY DATE
bf2020
  • 742
  • 4
  • 7
  • Updated to include omitted outer GROUP BY – bf2020 Feb 04 '14 at 21:36
  • Yes, but this is Excel? – aggFTW Feb 04 '14 at 23:21
  • I haven't tried this but it looks like it can help if you decide to go that route: http://stackoverflow.com/questions/8756802/excel-function-to-make-sql-like-queries-on-worksheet-data/8756874#8756874 – bf2020 Feb 05 '14 at 02:45
  • If that doesn't work, are you averse to using functions(in extra columns) and sorting in the source sheet? – bf2020 Feb 05 '14 at 03:03
  • Thanks, I'll take a look at using SQL directly. I could also just use the SQL CSV importer for SQL Server, but I thought this would exist for a pivot table. Using extra columns is doable, I know how. It's just that the approach is not robust and I want to be able to tune things very easily. Thanks! – aggFTW Feb 05 '14 at 03:36
  • To make pivot tables work - how about sorting by column A then subsort by B then subsort by C. Then add a function to D2 and then pasting down: `if(AND(A2=A3,B2=B3),0,1)` The value of 1 in column D would then indicate if the cell contained the "max" value(but actually just last in the group). In your pivot table you could sum and then filter by that D value. – bf2020 Feb 05 '14 at 15:15