Inventory Management: Collisions

[Amy_C]

Hi everyone.

I was hoping somebody could help me with the following.

I’m part of a cloud computing business where customers rent our machines. Machines are rented by daily slots. I’d like a way for us to be able to track our inventory so that our local coordinators are able to visually see which slots are available and book them in. Dark grey signifies a booked slot and light grey signifies a reserved slot:

In the above example, Project Stream for Machine 3 has been booked in as confirmed. Project River is then booked in as reserved however there are collisions on the 3rd and the 5th.

One of my ideas is to have the local reps fill in an excel form within their directory on the network. So a template with headers: Machine #, Project Name, Start Date, End Date, Notes.

A process is then created which collects all of these files and outputs the raw data which I can then feed into Tableau, create a dashboard like the above and then onto the server for the local reps to be able to use. When reps want to edit or delete an entry they simply delete the file off their network directory. I could then run a process once a day or whenever needed. I'd also like a way for our reps to be able to click on a booking and be able to view what dates the collisions occur so they can easily relay this back to the customers.

Is this the best way to approach this problem? Any help or advice is appreciated.

Thank you for reading this!

[DataBlender]

Hi @Amy_C

Your approach sounds sensible and possible as a way of capturing data but..

is there any reason why the reps can't all access the same template? It seems like this would short circuit any possibility of double booking machines if the rep can already see that a machine has been reserved for the required time.

[Amy_C]

We have a vast number of machines and bookings can be very long and complex, such as a few days here a few days there etc. The same booking can also cover multiple machines. Combined with the number of bookings a day and amount of reps, this becomes unmanageable. 

My idea for them to individually create files for a booking is because that may be a way for them to manage bookings easier.

How would I go about created this flow and to create a calendar with daily slots, will I need to prepare the data before it's fed into Tableau?

[DataBlender]

Ok, a few more thoughts from me (I'm hoping these might help steer you in the right direction rather than just be annoying!).

- Do reps assign specific machines to projects or are they allocated centrally? Either way, how? 

I can imagine that avoidable clashes will arise where reps have 'chosen' the same machine in their template but there are other available machines. I guess that this dashboard is meant to be the way in which reps can see what's available but there will probably be a timing difference between when the rep makes a booking in their template and when the dashboard gets updated.

- for the template, you'll want to make your life easier for collating the data so a tabular structure is good (as in your suggested columns).

- are you suggesting a new file for each booking or one booking file per rep with all bookings within it. Deleting the file means that you won't be able to look backwards, perhaps another field for active/inactive might give you more flexibility?

Once you get to Tableau, a gantt chart would be my first port of call.

[alex]

Can be solved in a couple different ways, but it depends on how the input file would work.  Can you mock the input in an excel workbook and attach?  Also if you have a screen shot or two of how the results would look in Tableau to make sure the data file is shaped correctly.

[alex · Accepted solution]

The attached workflow gives you 3 options based on how your data is formatted.

As a transaction type table

Cross tab format

Or as a table with start and end dates

All will throw out the following output

1= reserved

or as a report showing collisions (double booked)

Is this the solution you are looking for?

[Amy_C]

Hi Alex,

Firstly I just want to say how much I admire your Alteryx skills! The solutions you proposed are fantastic and the different data source formats give me room to investigate what would be most ideal.

I like the use of the summarize tool and and reservation count field to identify collisions. I especially like the solution to the third data format. The use of the generate rows tool to produce the end result is ingenious. 

This helps a lot and is a great starting point in the project. 

I just have two follow up questions and I think I'm set with this.

1) In the above process, we view collisions by Machine. How could I instead view collisions by Reserved Projects? So a single Reserved Project showing what dates and machines the collisions occur on.

2) Is the calendar view output structured in the best way to be able to be fed into a viz software like Tableau? I'd like the view to be similar to the one in my original post, with a list of all of our machines and projects on the side and on top the dates spanning into the future on a scroll (this should be okay because the scroll only need to go so far as the max end date). Open to suggestions on this!

Thank you again Alex, one day I hope to be as good as you!

[Amy_C]

Hi DataBlender,

Not at all annoying! Love these ideas!

- Reps need to assign specific machines to projects. It's a bit more complex than I described but to keep the example simple let's assume the reps do assign manually. Correct the dashboard is meant to be the way to see whats available. That is a good point, I need to factor in a timing difference. Going back to your first point about a single template I need to think about what works best here. Same thing about your last point regarding multiple files or a single file per rep.

What do you think about creating some type of booking form, similar to surveys, and having the results sent to me. Then I can timestamp entries and allocate accordingly.But this takes any editing control out of the hands of the reps. Hmm need to think about this. 

Edit: I don't know much about Analytical Apps but thinking there might be an idea in uploading this somewhere with various entry options.

[alex]

Thanks for the compliments!  If you  add an additional summarize tool (or change the existing one) at the same place as the current final summarize tool and configure it as shown.

Then you will get this output if it was there was a conflict.  I added a row to the original data set that showed project stream reserved for the same day on a different machine to mock up this collision.

As for tableau, it depends on the entirety of the workbook.  I'll send that under a separate reply later this morning.

[alex · Accepted solution]

 By adding an additional branch to the workflow you can save it as a tde file.  I prefer using the format of the last workflow and modifying to not filter out all the dates.

I'm a little rusty on my Tableau work, but I went with using Level of Detail formulas to identify conflicts  and came up with these visualizations.

very simplistic views - if more than one row per dimension or overlap in the schedule then there is a conflict.

I used the following LOD expressions. 

Conflicts - Date, Machine { FIXED [Date],[Machine]:SUM([Reservation])}

and

Conflicts - Date, Project { FIXED [Date],[Project]:SUM([Reservation])}

Not sure if this is the best or most elegant way, but it is one way to get you answers.