AND (Claims.ClaimOccurranceDate >= Transactions.TrueInceptionDate)
AND (Transactions.BusinessTransactionTypeName='NB' OR Transactions.BusinessTransactionTypeName='RENEWAL'),1,0))
The Claims.ClaimFileDate is derived from the file name of a document which is date stamped, the Picture Date is driven from a Calendar Object.
The above expression works fine as long as there is a file for a particular day, however, I only receive a file periodically and so I want to adjust the above so that If the Picture date is greater than the most recent file date I have it uses this file to do it's calculations.
I can't say =SUM(IF((Claims.ClaimFileDate) < ($(PictureDate)) because it will then sum every file that I have that is under the Picture date, I just want it to identify the most recent file that is Older than the picture date. EG
Picture Date 01/08/2012
Would look up File of 29/07/2012 and use this as the file
Picture Date of 07/08/2012 would use 04/08/2012 etc.
Your example won't work, This will return all Claim file dates less than the max picture date - my current example says when the Claims.Claimfiledate = picture date in calendar, and the Claims.ClaimOccurranceDate is less than Transactions.TrueInceptionDate AND Transactions.BusinessTransactionTypeName =NB or RENEWAL give it a 1, else give it a zero, I am then summing the 1's.
As I say, where the Claims.ClaimFileDate = the Picturedate in my expression works, but I don't always get a file and so I want to say go to the closest one to the picturedate selected as long as the claimsfile date is = to or older than the picture date, but I can't use <= as this will go to every record where the date is older than the picture date, thus duplicating the sum as the same record will appear in every folder but the value within it will alter.