set analysis not possible ? implicit field value definition - date less than dimension

[#1]

Hello,

Thanks for reading my (first !) message. Here's my question:

I have a table of Work orders, identified by WONUM (work order number/ID), which have a REPORTDATE (date created) and a FINISHDATE (date closed).

For each WONUM, I want to return the maximum FINISHDATE for the set of WONUM which have REPORTDATE before that of this WONUM.

For instance with the table below, I have given the wanted results in the last column: I now need to code this last column.

WONUM	REPORTDATE	FINISHDATE	Max finishdate of WONUM reported before
BC142529	2011/08/01	2011/08/04	2011/08/04
BC142796	2011/08/02	2011/08/02	2011/08/04
BC142805	2011/08/03	2011/08/11	2011/08/11
BC142808	2011/08/04	2011/08/05	2011/08/11
BC143523	2011/08/05	2011/08/12	2011/08/12

I have tried with Set analysis to return a subset of WONUM which have a REPORTDATE before that of the current WO, with no sucess (no aggregation). Also I cannot/dont want to use getfieldselection functions and such as this is independent of the selection(s).

I got so far (I'm not sure this is the best way to approach this):

max(

{$<WONUM =

P({1<REPORTDATE={"<=$(= ///// here REPORTDATE OF THIS WONUM as dynamic value ////// ))"}>} )

>} 

total FINISHDATE)

>> Return max of FINISHDATE from selection where (WONUM which have a REPORTDATE before that of this WONUM)

But no luck so far, I hope you have some ideas !

Thanks

Arthur

[#2]

Hi

I have attached a .qvw. I'd really appreciate your help !

Thanks

Arthur

[#3 · Accepted solution]

I found the solution.

I loaded the REPORTDATE and FINISHDATE twice, with no link between the two.

Then compare the dates to return the max FINISHDATE for REPORDATE before.