Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
jdmarlin
New Contributor III

Sum by max per group

I have the following data:

IDTimeValue
A26100
A27110
A28120
B150
B260
B370
C12200
C13210
C14190

And I'd like to transform it into:

IDTime 1Time 2Time 3
A100110120
B506070
C200210190

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)

Tags (4)
1 Reply
Highlighted

Re: Sum by max per group

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)