1 2 Previous Next 28 Replies Latest reply on Apr 9, 2012 5:14 AM by ethan.chernin

    Scheduling Gantt Chart

      I am trying to get a visual representation of a scheduling template (appointments filled vs appointments available). For example, I would like to show with a gantt chart "underneath" the total available slots and on top of it the appointments filled. Visit Type is also very important (VT in the data). I have provided a paint picture with a simple example of what I would like to accomplish.

      Ideas?

      Ethan

        • 1. Re: Scheduling Gantt Chart

          This is what I have so far...

          Cant seem to add the length to the chart (an appointment that starts at 8:30am that is 30mins long should show until 9am) filling in the white space.

          • 2. Re: Scheduling Gantt Chart
            Alex Kerin

            You need a calculated field that divides length by (60*24) and then drag that to the size pill.

            • 3. Re: Scheduling Gantt Chart

              Hi Alex-

              I actually already tried that, however, it did not work?

              • 4. Re: Scheduling Gantt Chart

                Not only are the hours wrong once that calculation is added, which length do you use...template or actual?

                • 5. Re: Scheduling Gantt Chart
                  Alex Kerin

                  Can you have a look at the data that is in Tableau? I have a suspicion your join is creating multiple rows for the same appointment. You have 588 rows for that single day.

                   

                  data.png

                  • 6. Re: Scheduling Gantt Chart

                    This makes no sense to me. I have no idea why there are so many rows? There should at most only be 29.

                    Any suggestions on the join?

                    Ethan

                    • 7. Re: Scheduling Gantt Chart
                      Alex Kerin

                      This is a problem for any database (the join is occurring at the db level, not in Tableau) - you are not joining on a unique field that exists in both, you are joining on a non-unique field. [provider] from your first table is matched to every row on your second table. Therefore you get 21 rows times 28 rows = 588 in the joined table.

                       

                      You could try data blending - see attached, but I don't know what your end goal is for the chart.

                      • 8. Re: Scheduling Gantt Chart

                        Getting there!

                        End goal is one gantt chart. So if we used your twbx file as an example, I want sheet Sheet 2 (template appointments) to be the "behind color" lets say red and Sheet 1 overlapped on top (actual scheduled appointments) in green.

                         

                        As a result if you see any red  you could easily see they did not fill a specific time with an actual appointment.

                         

                        I really appreciate your help Alex,

                        Ethan

                        • 9. Re: Scheduling Gantt Chart
                          Alex Kerin

                          That's what I suspected - actually a pretty tricky problem as we need to somehow treat this as a queue problem or pad the data with times to properly solve it, and with the 2 data sources that really gets interesting.

                           

                          Do slot times always match an actual time? It appears they do in this dataset, which is why the attached sort of works. If they do, maybe there's something else we can do.

                          • 10. Re: Scheduling Gantt Chart

                            If I understand your question correctly, slot times will not always match actual times.

                             

                            Slot times = all available appointments (aka template hours) and will be the same all the time.

                            Actual times = all filled appointments (filled hours) and varies.

                             

                            Ideally a great clinic would fill all available appointments!

                             

                            This is what I have come up with based on playing with your file. PROBLEM NOW is adding VT as a color as well. For example from 12-1 it shows "null" or in this case open but really it is an unavailable working slot and should be another color

                             

                            Getting close but not quite there...

                            Ethan

                            • 11. Re: Scheduling Gantt Chart
                              Alex Kerin

                              If they vary, this method just isn't a great way to do this - I tried joining on the time as well - I only get 30 rows, but I don't think this is the right way to do it either. I'm stuck with this at the moment and suspect that we need custom SQL like your utilization question in early March. Wonder if Jonathan Drummey has any ideas?

                              • 12. Re: Scheduling Gantt Chart
                                Jonathan Drummey

                                I was going to suggest a union query, and then I thought I'd just do it to see if it worked. See the attached.

                                 

                                We can do a dual-axis chart with blended data, but this data set is looking for two dual axes - times and measures from both data sets, and that means Tableau will end up drawing measures for each time axis and that's not going to be correct. Really, the data sets of Actual and Template are independent data sets and we just want to draw one on top of the other.

                                 

                                So I created some Custom SQL to UNION the two data sets together, with two new fields to identify the source Actual/Template and Color (Unique from Actual, VT from Template). To get the colors to draw right, I created a Set in Tableau and sorted that ascending alphabetically, and it's the Set that is actually on the Color shelf. That way Tableau will always draw the Actual data on top.

                                 

                                Jonathan

                                • 13. Re: Scheduling Gantt Chart
                                  Jonathan Drummey

                                  Hi Alex, thanks for thinking of me! I'd actually already started work on this before you posted, I'd been fighting with some of my own SQL code and decided to take a break by working with someone else's (hopefully easier) problem.

                                   

                                  Jonathan

                                  • 14. Re: Scheduling Gantt Chart

                                    Jonathan, you've done it again! True Tableau master.

                                     

                                    My only remaining question would be is there a way to make it so the color is on Template VT and not be each unique record.

                                    For example:

                                    All the "actual's" grouped as blue and classified as "filled"

                                    All the "Template's " grouped as red and classified "open"

                                    But if "unavailable" then grouped as green and classified "unavailable"

                                     

                                    I know I could do it manually by editing colors but not sure there was a way to do it otherwise.

                                     

                                    Thanks again Jonathan!

                                    Ethan

                                    1 2 Previous Next