topic Re: Aggr() function in QlikView

Aggr() function

Anonymous — Tue, 15 Dec 2015 11:41:28 GMT

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 — Tue, 15 Dec 2015 12:04:06 GMT

I think you just need FirstSortedValue() function:

Dimension: ID

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

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

Anonymous — Tue, 15 Dec 2015 12:05:57 GMT

Thanks Sunny!

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

avinashelite — Tue, 15 Dec 2015 12:09:31 GMT

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

CustomerID needs to be in the dimensions list

sunny_talwar — Tue, 15 Dec 2015 12:43:41 GMT

This as your dimension:

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

Anonymous — Tue, 15 Dec 2015 14:37:50 GMT

Thank you so much!

sunny_talwar — Tue, 15 Dec 2015 16:05:20 GMT

Your welcome