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

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

    Lucas Cesar

      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)