4 Replies Latest reply: Aug 10, 2012 4:55 AM by kevinsmith RSS

    Max Date or Closest Date

      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.

        • Re: Max Date or Closest Date
          Matthew 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

            • Re: Max Date or Closest Date

              Hi Matt,

               

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

               

               

              Thanks

               

              Kev.

                • Re: Max Date or Closest Date
                  Matthew 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

                    • Re: Max Date or Closest Date

                      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.