5 Replies Latest reply on Jan 31, 2013 8:50 AM by Matthew Hillis

    Days in Application Calculation

    Matthew Hillis

      I have a Microsoft SQL server that contains all of my Mortgage Pipeline Data.  I am currently trying to create a PIE chart that shows how many days a loan has been in Application.  The data tells me it is an Application if there is a date in my [Application Date] and NO date in [Closed Date].  I want to show how many loans have been in Application for under 30 days, 60 - 90 Days and then 90+.

        • 1. Re: Days in Application Calculation
          Justin Larson

          Sounds like you need an age calculation. You can use the DATEDIFF function.

           

          So just an age calculation would look like this:

          DATEDIFF("day",[Application Date],TODAY()  )

           

          which returns the number of days between now and the app date. If you want the results bucketed in non-regular buckets (bins), you could nest a evaluation of the age and return a bucket value.

           

          if DATEDIFF("day",[Application Date],TODAY()  ) <30 then "30-"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) < 60 then "30-60"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) <90 then "60-90"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) >90 then "90+"

          end

           

          In this example, it returns text, but you could format the output however you want.

           

          To keep results limited to non-closed, you could just use the filter to filter out non-null closed applications. Alternatively, if you want to keep it working even without the filter, you could nest an evaluation of the [Closed Date] to change the result of closed applications, for example:

           

          If isnull([CloseDate]) then null

          elseif DATEDIFF("day",[Application Date],TODAY()  ) <30 then "30-"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) < 60 then "30-60"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) <90 then "60-90"

          elseif DATEDIFF("day",[Application Date],TODAY()  ) >90 then "90+"

          end

          • 2. Re: Days in Application Calculation
            Alex Kerin

            Can you provide an example of a few lines of data?

             

            Edit: If  the above solution does not work.

            • 3. Re: Days in Application Calculation
              Matthew Hillis

              I think that is getting very close.  I used the bottom Formula and placed it into the Columns Self.  It just drew a straight line but when I went into the underlying data I found 204 records.  Looking through the data it did not ignore the [Closed Date].

               

               

              I have attached a scrubbed excel file that shows the data and fields.

              • 4. Re: Days in Application Calculation
                Justin Larson

                I guess I kind of misspoke by saying the calculation would ignore closed. In reality it reports back closed apps as Null. In any case, you can throw number of records in a pie chart, colored by your new field (I failed to name it, but it would be "App Age Buckets" or something to that effect). Filter out null values from the "App Age Bucket."

                 

                Looks like you have an app status in that sample dataset as well. Using a filter on that field for non-closed apps should yield the same result.

                 

                If you really really don't want to use filters, you could create a second calculated field that only reports back a number if the closed date is null, then aggregate on that, but color your chart on the previous calculated field that shows buckets.

                 

                It would look something like:

                If isnull([CloseDate]) then 1 else 0 end

                • 5. Re: Days in Application Calculation
                  Matthew Hillis

                  Thanks that worked.  I used the Closed Date as the the filter.  I was not able to filter on the Current Status because that one is constantly changing.  Thank you so much.