Max Date or Closest Date

[#1]

Hi All,

I currently have the following Expression -

=SUM(IF((Claims.ClaimFileDate) = ($(PictureDate))

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

File Dates

22/07/2012

29/07/2012

04/08/2012

09/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.

Thanks in advance for your help.

[matt_crowther]

To generate the relevant 'File Date' I've used the following expression:

=date(max(if([File Date]<=v_PictureDate,[File Date])))

Put this in you're variable used in your current expression and it should work.

Hope it helps,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

[#3]

Hi Matt,

Not sure where this needs to go in my Expression above?

Thanks

Kev.

[matt_crowther]

Kevin,

Place =date(max(if([File Date]<=v_PictureDate,[File Date]))) as a variable, let's say 'v_Max_Pic_Date'

You're expression would then be something similar to:

=SUM(IF(Claims.ClaimFileDate<v_Max_Pic_Date,[What you want to sum]))

From you inital examples I can't see any value that you're summing up so that may also be where you're going wrong.

Hope that helps,

Matt - Visual Analytics Ltd

@QlikviewBI

[#5]

Hi Matt,

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.

Thanks

Kev.