Qlik Community

Qlik Sense App Development

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

lucasrcesar
New Contributor

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)

3 Replies

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]))

lucasrcesar
New Contributor

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

Worked. Thanks Sunny!!

Community Browser