topic Re: Aggregating based on max timestamp in App Development

Aggregating based on max timestamp

m_perreault — Fri, 06 Apr 2018 16:51:40 GMT

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.

ID	Customers	UpdateTime
1	10	2018-01-03 00:00:00
1	15	2018-01-02 00:00:00
1	20	2018-01-01 00:00:00
2	5	2017-01-02 00:00:00
2	8	2017-01-03 00:00:00
2	50	2017-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

Re: Aggregating based on max timestamp

sunny_talwar — Fri, 06 Apr 2018 16:53:32 GMT

May be this

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

Re: Aggregating based on max timestamp

sunny_talwar — Fri, 06 Apr 2018 16:54:26 GMT

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

Re: Aggregating based on max timestamp

m_perreault — Fri, 06 Apr 2018 16:57:02 GMT

Both options seem to work thank you!