Date Filter Urgent

building reports webintelligence
1

Hi Everyone,

Hope someone can help me on this, I’m having problems trying to filtering by the oldest invoice on each store.

So lets say we have this table

Store Inv. No Invoice Date
A 123 10/19/2010
A 234 2/1/2011
A 434 4/11/2011
B 566 10/8/2010
B 234 12/21/2010
C 544 10/8/2010
C 355 12/21/2010
D 677 10/19/2010
D 899 12/21/2010
E 222 10/19/2010
E 111 12/21/2010
F 344 10/8/2010
F 233 2/1/2011
G 999 10/8/2010
G 555 11/11/2010

I want to find out what formula i need to use, to put as filter to get the result shown below:

Store Inv. N. Invoice Date
A 123 10/19/2010
B 566 10/8/2010
C 544 10/8/2010
D 677 10/19/2010
E 222 10/19/2010
F 344 10/8/2010
G 999 10/8/2010

Basically what i need is just showing the invoice with the oldest date on each store, i already tried everything that i know i think, i used the rank formula, i also tried to make a formula using MIN(Inv. Date) in Store, but what i really dont find is how to use that as a filter.

Please let me know if someone knows how to fix this issue!

Thanks in advance!

dacuna :costa_rica: (BOB member since 2011-03-24)

2

I believe webi see’s the date as a string, so the min won’t work straight away. Perhaps try turning it into a date first with todate() then a sort on it

ally3009 :uk: (BOB member since 2010-08-10)