4 Replies Latest reply: May 15, 2014 9:57 AM by Diane Miller RSS

    Max Function

    Diane Miller

      I am using the Max function to identify the most recent date but when one of my date fields is blank, it is returning (NULL). My calcuated field is set up as a date field with the expression "Max(,[Last Pmt Date],[Open Date])"

      Any ideas?

        • Max Function
          Grant Perkins

          Hi Diane and welcome to the forum.

           

          The null is the result of Monarch trying to perform a numerical calculation on a non-numeric field.

           

          There are two options I can think of to deal with that, there may be others.

           

          The first options is to filter out the record with blank dates since the calculation cannot be performed. If doing something with a VALID date was key to the process this could make a lot of sense. The filter would either be in the form of a Monarch filter to eliminate rows with empty dates in that field.

           

          Alternatively use a conditional filter as part of the formula to set an alternative pre-defined date value without hiding the record. Use an IF() statement to check the validity of the date field  and then set a fixed (or alterntiveluy calculated Some may even be the same ...    

           

          Other suggestions may well be offered.

           

          HTH.

           

           

          Grant

          • Max Function
            Diane Miller

            Thanks that helped. Here is what I ended up writting:

             

            max(if(isnull(),{01/01/1976},[Last Pmt Date]),if(isnull(),{01/01/1976},[Last Activity Date]),if(isnull(),{01/01/1976},[Open Date]))

             

            Not pretty but it worked!   [img]smile.gif[/img]

            • Max Function
              Grant Perkins

              Originally posted by Diane Miller:

              Thanks that helped. Here is what I ended up writting:

               

              max(if(isnull(),{01/01/1976},[Last Pmt Date]),if(isnull(),{01/01/1976},[Last Activity Date]),if(isnull(),{01/01/1976},[Open Date]))

               

              Not pretty but it worked!     [img]smile.gif[/img]   /b[/quote]Doesn't matter if it is not pretty if you don't regularly see it!

               

              Isnull() should really have a value for the result so either a 0 or 1

               

              From the Help for the ISNULL() function

               

              "This function returns 0 (zero) indicating that expression is not null or 1 (Boolean True) indicating that expression is null."

               

              So I think your formula should read

               

              max(if(isnull()=1,{01/01/1976},[Last Pmt Date]),if(isnull()=1,{01/01/1976},[Last Activity Date]),if(isnull()=1,{01/01/1976},[Open Date]))

               

               

              It may be that the default is 1 and so it works anyway but including the value being checked for makes future interpretation a little easier if nothing else.

               

              HTH.

               

               

              Grant

               

              [size="1"][ September 17, 2007, 03:13 PM: Message edited by: Grant Perkins ][/size]

              • Max Function
                Diane Miller

                Thanks for the feedback.