Hi,
Im trying to create a report in Deski, which contains the Latest Date and the Immediately Previous Date from the same table.
For eg:
This is the Table Ive
Name Rating Rating_Date
John 5 8/6/2010
John 6 14/5/2010
John 4 10/6/2011
Kevin 2 11/2/2010
Kevin 1 12/5/2011
The Result Im expecting is like below:
Name Latest_Rating Latest_Rtng_Date Previous_Rtng Previous_Rtng_Date
John 4 10/6/2011 5 8/6/2010
Kevin 1 12/5/2011 2 11/2/2010
I used 2 data providers, with Query1 taking the values with Max(Rating_Date), another query (Query2) with Rating_Date < (Rating_Date from Query1). I linked these 2 on Name.
To get the second Max Rating_date for each Name, I created another variable Previous_Rtng_date = Max(<Rating_Date (Query 2)>). It seemed to work for a moment, but as soon as I pulled in the Previous Rating, it gave me multiple row results!
Name Latest_Rating Latest_Rtng_Date Previous_Rtng Previous_Rtng_Date
John 4 10/6/2011 5 8/6/2010
John 4 10/6/2011 6 14/5/2010
Kevin 1 12/5/2011 2 11/2/2010
Can anyone guide me on this please? Not able to get through this for some time now… :?
Thanks for your response!
Kash D (BOB member since 2011-07-11)