3 Replies Latest reply on Feb 13, 2013 2:02 AM by Dana Withers

    How to count days and condition it to stop counting when a condition has been met?

    john.taylor.0

      Greetings!

       

      I work for a mortgage loan company, and my task is to generate a report that shows several phases of the mortgage loan origination process and the number of days it is in each phase.

       

      The phases are below:

       

      Prospect  I  Application  I  Sent to Processing  I  Submitted to Underwriting  I  Locked  I  Closed

       

      What I'm trying to do is based is count how many days the loan has been in each phase, but stop the count for a particular phase when it reaches the next phase. For example, I want to see a finite number of days the loan was in the application phase, not a running tab. When it hits "sent to processing," the count needs to stop for application.

       

      The goal here is to help identify stages where the loan process is taking more than 8 days (per stage). I'd love to be able to conditionally color anything over 8 days so that it is obvious that this phase failed at the overall goal of 7 days per stage, but I didn't know if Tableau can do this or not.

       

      I've attached the workbook. The sheet is named "Tableau Help"

       

      Thanks in advance!

       

      John

        • 1. Re: How to count days and condition it to stop counting when a condition has been met?
          Joshua Milligan

          John,

           

          Take a look at the attached workbook (looks like you are using Tableau 8 beta?  Hopefully you won't have any problems opening it).

           

          I've shown a possible approach.  I think you were really close.  The main difference is that I calculated the difference between the dates of each phase instead of the the difference from the phase to today.

           

           

          A couple of notes:

          • I didn't include the locked to closed for highlighting over 8 days as that phase looked to take around 20 days.  Maybe that wasn't originally included in your view.
          • I parameterized the number of days threshold.  You could change the calculations to hard-code the value if it won't ever change.

           

           

          Joshua

          1 of 1 people found this helpful
          • 2. Re: How to count days and condition it to stop counting when a condition has been met?
            john.taylor.0

            Joshua,

             

            Thank you for your assistance!

             

            I took a look at your workbook and if I'm not mistaken, it does not calculate a value for the current phase. For example, when a loan is in the processing phase, even though a date is populated, it does not show a running number. I assume this is because it is attempting to calculate based on a "future" date that is not there.

             

            In other words, your solution works perfectly assuming we have dates populated in every status field (as is the case with the dummy data I provided.) However our real data will not be this way and will be more progressive when it comes to the timeline. This is meant to allow our managers to monitor the loan process as it occurs to ensure the loan life-cycle is completed in an acceptable timeframe.

             

            With that being said, I think I've gotten pretty far into the solution. Unfortunately I have one last hurdle I can't seem to get over.

             

            I've created two sets of calculated fields:

             

            Count X: Counts the DateDiff between X phase and today.

            Days in X: Counts the number of days between X phase and next phase.

             

            Then I've created another "Final" Calculation with an IFNULL that determines which of the values above to show for a given phase.

             

            Now, I'm trying to calculate a total number of days even for loans that have not completed the loan life cycle yet. The problem is I can't figure out how to sum the phases because each phase could be populated with one of two pieces of data (depending on what conditions were met in the IFNULL).

             

            I've attached the workbook again and would appreciate another glance from anyone interested in helping. It is much appreciated.

             

            John

            • 3. Re: How to count days and condition it to stop counting when a condition has been met?
              Dana Withers

              Hi John,

               

              I started playing around with it yesterday and took a slightly different approach. I finished and attached it anyway in case a different look helps either in this case or for another one. (nice play for me anyway)

               

              However your final question about the total is not as complicated as it sounds - there is a ZN function that fills in 0 if it encounters NULL. Add that to your calculation and the totals add up I think.

              I've added that to your book and attached it as well.

               

              Hope that helps!

               

              Dana