10 Replies Latest reply on Aug 1, 2017 10:13 AM by Michael Hesser

    Visualising duration - Brain Teaser

    Łukasz Majewski

      Hi,

       

      I thought I could share this little brain teaser with you.

       

      Here is your data which you are not allowed to modify (xls attached too):

       

      IDStartEnd
      111:27:4915:13:35
      200:47:1001:30:00
      321:13:3823:41:49
      401:18:1910:31:46
      503:26:2314:04:30
      600:20:5609:56:31
      723:54:3608:52:43
      818:26:5601:10:13
      910:21:5422:11:29
      1007:42:4416:32:48
      1116:26:4201:07:23
      1216:45:1600:44:36
      1303:46:3104:44:34
      1412:21:3413:28:40
      1515:57:4916:59:43
      1623:47:3507:18:56
      1712:21:3323:09:51
      1800:52:3011:33:14
      1904:10:0511:12:30
      2015:21:1423:18:49

       

      Goal: visualize duration - all are <24 hours - of all the records like this:

      durations.png

       

      it should behave like this when interacting with it:

      durations.gif

       

      The arrows should be roughly evenly distributed vertically depending on their count (here 20).

      Notice the 24h horizontal axis is continuous hence the arrows are repeated depending on current zoom level. It may be limited to only show 24h too:

      durations24.png

        • 1. Re: Visualising duration - Brain Teaser
          Gerardo Varela

          I gave it a go but I didn't get very far. I did learn that I've never really dealt with just time data so I have something to work on. Thanks for that! Here is as far as I got in my attempt yesterday. 

           

           

           

          I have no idea how you managed to get the vertical grid marks to stay put.      I suspect your going to blow my mind away like you did in your last brain "teaser". 

           

          I do have some assumptions on how you are doing the above. 

           

          Your using your parameter technique to extend the time duration for the full 24 hr period.

          There is a cross database join with clipboard data to add the paths.

          My gut tells me reference lines for the vertical grid marks or some sort of magical use of bins?

           

          I'll patiently wait to have my brain melted yet again

           

          Regards,

          Gerardo

          • 2. Re: Visualising duration - Brain Teaser
            Łukasz Majewski

            Thanks for giving it a try.

            I am not using any db joins though it would help indeed. Actually cross join enabled since 10.2 is a more elegant way of duplicating rows for making lines.

            One of the reasons I published this is I hope someone shows a better way of achieveing my goal - there are at least 2 issues I worked around rather than did as I would like to.

            Oh, I think this one is not that twisted as my previous one

            • 3. Re: Visualising duration - Brain Teaser
              Annabelle Rincon

              interesting.. I can't wait for the answer.

              • 4. Re: Visualising duration - Brain Teaser
                Egor Larin

                Crazy one...

                 

                What i can't solve:

                • Show lines again when you reach the end of timeline. That effect can happen only on Maps. So my gues you repeated it 10 times (or less ) I don't know. Also it will give a huge limitation of number of movements to the edge of 25 hours
                • You work with background image - only that kind of zoom is available for maps and background images. I have no idea how you repeat the marks
                • Convert Int Axis to hh:mm:ss, I think it is better to work with seconds. Like 00:00:00 is 0 , 23:59:59 is 86399 and so on. Didn't find a chance to convert it using custom Format

                 

                Would be great to see your solution.

                • 5. Re: Visualising duration - Brain Teaser
                  Łukasz Majewski

                  You were close

                  Let me ping Michael Hesser who wanted me to challenge him, I think.

                  I am going to reveal my solution tomorrow, August 1st at 17 CET

                  • 6. Re: Visualising duration - Brain Teaser
                    Michael Hesser

                    WOWSERS, Łukasz Majewski !

                    To be clear-- I didn't want you to challenge ME (this certainly does it-- and THEN some... I won't try to be humble!), but rather present a challenge for everyone.

                     

                    Here are my assumptions-- and boy howdy, I may be thinking crazy thoughts!:

                    • Clocks aren't the only thing that use hours/minutes/seconds-- maps do, too. My guess is that you're using Tableau's Symbol Mal for your own nefarious purposes
                    • So if you're using a map, "Where is my house-- or my continent, for that matter?!?"  I don't think this is as simple as blacking them out & hiding the edges. Alternatively, I'm sure I saw an article here that showed how you can just "remove" the map and still use the properties of the graph. I din't fully read the article (I hope you weren't the author) but I think it said when you remove lat/long from a symbol map, it still "works". This would explain how you kept your custom axes-- a "custom map".
                    • If you're using an "official map":
                      • You could multiply Start and End "times" by 15 to generate a number between 0 and 360. Then you subtract 180 to generate a Longitude between -180 and 180. (I'm not too sure about this-- this might make things squirrelly and a slightly more complex formula would be needed). You wouldn't need this step if you're using your own custom map.
                      • You're keeping entries evenly spaced by counting them, then using a little formula to generate Latitude (maybe between +/- 100 degrees?). A slightly different formula would be needed for a custom map.
                      • I think you'd be using a Mercator projection-- I'm guessing this because you want your Lat/Long in squares BUT the poles can't be represented. This is why you chose not to extend your lines to the top & bottom of your graph-- you couldn't! Or maybe a Equirectangular map?
                    • Once you have your Starting/Ending Lat/Long, you're plotting the lines on a dual-axis map. I'm thinking the dual-axis allows you to add a SHAPE to your end point?
                    • You've selected a map with repeating edges (that way you time periods can magically repeat). This also means highlighting one line highlights all of her "sisters" since it is really just the same line, duplicated.
                    • Pan & zoom of the map allows you to drill down.

                     

                    As you can see, I'm making a ton of crazy guesses here. Go ahead-- show me your amazing magic!

                    • 7. Re: Visualising duration - Brain Teaser
                      Egor Larin

                      This is exactly what I thought how it can be done. Only with Maps. And Lat/Long recalculations models.

                      Background picture will not work...

                      • 8. Re: Visualising duration - Brain Teaser
                        Yuriy Fal

                        The teaser is really hard, it requires a nutcracker (which I don't have).

                        Couldn't find a way to combine a map view with a timeline grid.

                        timestamps_01_map.png

                         

                        timestamps_02_timeline.png

                         

                        Gave up for now, waiting for the Master to come with the answer.

                        • 9. Re: Visualising duration - Brain Teaser
                          Łukasz Majewski

                          So, this is the time.

                           

                          Indeed I simply used maps background but took all the layers out so that it is not visible.

                           

                           

                          Longitude is translated from time as follows:

                           

                          Since I decided to use unchanged 20 records with my timestamps I had to densify my marks and the core calculations are:

                          and

                           

                          • split - to duplicate each row for lines
                          • s - to extend my dataset with 24 hours grid

                           

                          Bins are made out of these of course and only these are used as dimensions in the viz. INDEX() is used to densify to (20+24)x2 marks - this is where actual data resides along with densified marks:

                          I then used a number of calculations to identify and separate marks for my durations and the grid and yes I used dual axis to have arrow shapes on top of the lines. You may check all this in the attached workbook.

                           

                          I would just like to highlight 2 problems for which I am seeking another solution:

                           

                          1. Spacing lines evenly vertically in web mercator is not easy; distance distortion should be proportional to 1/cos(Lat) so after playing with it for a while I ended up with rather horrible formula:

                            it works quite well with my 20 lines but not too well in other cases;
                            How can this be perfected?
                            Can we work with another type of map projection in Tableau where that distortion is not existent?

                          2. 24 hour grid - I really wanted to have a background layer with such a grid and be able to customize the labels. Probably this could be achieved with a use of Mapbox custom map but I have not yet learned how to build something like that with a free account....

                           

                          Thanks everybody for participating