Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

m_perreault
Contributor II

Aggregating based on max timestamp

Hello All,

The data I am working with can be updated over time and its important that the aggregations being calculated are based on the most recent update.  So for example I have the following table below.

IDCustomersUpdateTime
1102018-01-03 00:00:00
1152018-01-02 00:00:00
1202018-01-01 00:00:00
252017-01-02 00:00:00
282017-01-03 00:00:00
2502017-01-01 00:00:00

I want to sum customers across ID's based on their max update time so from this table I want my sum of customers to equal 18.

10 from ID=1 where max Update is 2018-01-03 00:00:00 and 8 from ID=2 where max UpdateTime is 2017-01-03 00:00:00 .

How would I calculate this?

Thanks

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregating based on max timestamp

May be this

Sum(Aggr(FirstSortedValue(Customers, -UpdateTime), ID))

3 Replies
MVP
MVP

Re: Aggregating based on max timestamp

May be this

Sum(Aggr(FirstSortedValue(Customers, -UpdateTime), ID))

MVP
MVP

Re: Aggregating based on max timestamp

or

Sum(Aggr(If(UpdateTime = Max(TOTAL <ID> UpdateTime), Customers), ID, UpdateTime))

m_perreault
Contributor II

Re: Aggregating based on max timestamp

Both options seem to work thank you!

Community Browser