1 Reply Latest reply on Sep 26, 2017 3:00 PM by Shinichiro Murakami

    Overlapping timeperiods in a Ganttchart

    Henrik Eriksson

      Hello,

       

      Got a problem I'm not able to solve here.

      The basics is that we're converting an old application style report (done in C#) to Tableau, and we want to keep the general idea of the application.

      The application shows overlapping tasks as a Gantt style report, and so far so good.

      My problem that I can't seem to solve is how to order the overlapping timeperiods.

      I want to have it so that when one timeperiod overlaps with another it should populate a new row. When a third timeperiod overlaps with the second one, but not the first one, the third one should take the first available row (in this case row 1, as it doesn't overlap with it).

      It's when this happens that my SQL ability fails me, and as the original dataset is quite large, it starts to take up more and more power on the servers to fix this so I'm looking into a possible Tableau solution instead for the actual calculation.

      In the attached file I have an example of this issue (although the calculations are made in SQL and I'm just using randomised sample data here)

      As you can see the first timeperiod is in the first row, as it should. The second timeperiod overlaps with the first, and the third overlaps with the second only, and should be in the first row instead.

      The reason they overlap is because the SQL joins itself on the time, order by the starttime, and then ranks them. As there's one timeperiod before the third, it's ranked as row 2, while I want it to be ranked as row 1 (first available row).

      I should add that the number of overlaps can be many more than just 2 at the same time.

      Also, the sorting I have at the moment may or may not be irrelevant to an actual solution. I don't know how else to generate this, which is why I used my SQL solution to get the row numbers. Please feel free to completely ignore that column for a tableau specific calculation.

       

      I have no idea how to be able to solve this in Tableau, or even if it can be solved.

      Anyone have any ideas on how to do this?