Aggr() function

[Anonymous]

Hi,

I have a straight table with customer ID , date of invoices and store.

i want to use AGGR() function in order to show only one row with the last date of invoice.

Aggr(Max(InvoiceDate),CustomerID)

i guess i need another aggr() in order to get what i want..

anyone?

the table is like this:

ID | date of invoice | store

1 , 1/1/12 , AA

1 , 1/5/12 ,BB

2 , 1/1/13 , AC

i need :

ID | date of invoice | store

1 , 1/5/12 , BB

2 , 1/1/13 , AC

thanks,

[sunny_talwar · Accepted solution]

I think you just need FirstSortedValue() function:

Dimension: ID

Expression1: =Date(Max([date of invoice]))

Expression2: =FirstSortedValue(store, -[date of invoice])

[Anonymous]

Thanks Sunny!

But if i want to use AGGR - i need it in as dimension.. can i do it?

[avinashelite]

if you want to use this "Aggr(Max(InvoiceDate),CustomerID)" expression then

CustomerID needs to be in the dimensions list

[sunny_talwar]

This as your dimension:

=Aggr(FirstSortedValue(store, -[date of invoice]), ID)

[Anonymous]

Thank you so much!

[sunny_talwar]

Your welcome