Sum by max per group

jdmarlin — Tue, 26 Sep 2017 15:54:33 GMT

I have the following data:

And I'd like to transform it into:

Where Time 1, Time 2, and Time 3 are the first, second, and third max Time per ID. So for ID of A, Time 28 is Time 1. Time 27 is Time 2, etc

I've tried:

Time 1 = Sum({<Time={$(=Max(Time, 1))}>} Value)

Time 2 = Sum({<Time={$(=Max(Time, 2))}>} Value)

Time 3 = Sum({<Time={$(=Max(Time, 3))}>} Value)

Re: Sum by max per group

sunny_talwar — Tue, 26 Sep 2017 16:03:03 GMT

May be this

FirstSortedValue(Aggr(Sum(Value), ID, Time), -Aggr(Time, ID, Time), 1)

FirstSortedValue(Aggr(Sum(Value), ID, Time), -Aggr(Time, ID, Time), 2)

FirstSortedValue(Aggr(Sum(Value), ID, Time), -Aggr(Time, ID, Time), 3)