3 Replies Latest reply: Apr 12, 2018 2:16 PM by Lucas Cesar

# How to sum only the value, for each OrderNumber, of the latest date?

Hey everyone,

I’m working with a dataset like the one below, where I’ve all the transactions (OrderKey) for each Order (Order Number) and I want to sum the column “Value” to see what is my Revenue, but the Order can have more than one transaction recorded, this happens because the server retries the transaction if the transaction was not completed, as you can see as “OrderNumber_#”.

I have to sum only the value, for each OrderNumber, of the latest date. I did a highlight in the values that I want to sum.

Can someone help me with this matter?

 OrderKey Order Number Date Value 11111 11111 01JAN1712:00:00 100 11111_1 11111 01JAN1712:00:40 90 11112 11112 01JAN1712:30:00 300 11122 11122 01JAN1712:34:00 700 11232 11232 01JAN1712:34:00 400 11232_1 11232 01JAN1712:35:00 360 11232_2 11232 01JAN1712:35:10 320 11232_3 11232 01JAN1712:35:55 300 21211 21211 01JAN1715:00:00 1000

So in summary I want a KPI with the SUM of the specific Values, in this case would be 2.390 (90+300+700+300+1000= 2.390)

• ###### Re: How to sum only the value, for each OrderNumber, of the latest date?

May be this

Sum(Aggr(FirstSortedValue(Value, -Date), [Order Number]))

• ###### Re: How to sum only the value, for each OrderNumber, of the latest date?

Or this

Sum(Aggr(FirstSortedValue(Aggr(Sum(Value), [Order Number], Date), -Aggr(Date, [Order Number], Date)), [Order Number]))

• ###### Re: How to sum only the value, for each OrderNumber, of the latest date?

Worked. Thanks Sunny!!