1 of 1 people found this helpful
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.
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.
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!