topic Help with advanced aggr in QlikView

Help with advanced aggr

Wed, 11 May 2011 07:24:38 GMT

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

I have tried:

firstSortedValue(aggr(sum(Sales), OrderDate, Customer), -OrderDate)

Am I doing something wrong?

Thank you in advance

JM Patino

Wed, 11 May 2011 09:31:54 GMT

Just add a "distinct" statement in your expression :

firstSortedValue(distinct aggr(sum(Sales), OrderDate, Customer), -OrderDate)

Wed, 11 May 2011 09:53:53 GMT

Wow it worked. That was a bit counter intuitive. Anyway thanks.