Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I looking for a way to get the first/last invoice value of a customer:
For example: Given this table
Customer | OrderDate | Product | Value |
---|---|---|---|
A | 1/1/2011 | A | 30 |
B | 1/1/2010 | B | 30 |
B | 1/1/2010 | C | 40 |
C | 1/1/2011 | D | 30 |
C | 1/1/2010 | E | 40 |
I want to create a report with
Customer | Max(OrderDate) | Latest Order Value |
---|---|---|
A | 1/1/2011 | 30 |
B | 1/1/2010 | 70 |
C | 1/1/2011 | 30 |
I have tried:
firstSortedValue(aggr(sum(Sales), OrderDate, Customer), -OrderDate)
Am I doing something wrong?
Thank you in advance
JM Patino
Just add a "distinct" statement in your expression :
firstSortedValue(distinct aggr(sum(Sales), OrderDate, Customer), -OrderDate)
Just add a "distinct" statement in your expression :
firstSortedValue(distinct aggr(sum(Sales), OrderDate, Customer), -OrderDate)
Wow it worked. That was a bit counter intuitive. Anyway thanks.