I want to be able to track the on time performance of customers returning our equipment. When creating a project for returns,we create a schedule of how many assets a customer should send back each week, but it is up to the customer to choose which asset to send back. So on one hand, we have a naturally aggregated data set that cant be deconstructed to the asset level, since any asset left can technically fill a scheduled slot. Then I have aggregated the actual releases in an attempt to track a customer's performance based on the agreed upon schedule. However I have run into many issues with this and was wondering if Tableau was capable of creating a deaggregated data set from the schedules.(If that is even the best method to approach this issue)
To make things more convoluted, there are three scenarios that can occur upon release based on our ticketing process.(Ticket still not generated, generated late, or generated on release) Ideally our system opens a ticket when releases occur and captures that date to compare to the schedule and then we also track when that asset arrives at the predesignated shop and when we have completed any maintenance. There are also multiple schedule versions that the actual return numbers can be compared against, but I am focusing on "Last Approved Schedule" currently. I have been able to write the logic to differentiate those data points into the SQL query, so if you have a solution that requires some SQL logic to create dummy lines for the schedule, I am open to that as well.
My goal is to determine the weekly on time return performance but it is quite a pickle, as I need to compare the first actual return to the first possible scheduled return and so on depending on how many assets exist on a particular project. Maybe something with indexing or ranking would allow this, but I have not been able to wrap my head around it. Hopefully someone has solved a similar goal in the past and can shed some light.
Thank you and I have attached a dummy data set representing a current project that is having the customer release assets to 4 different shop locations.