5 Replies Latest reply on May 5, 2017 8:21 AM by Jonathan Drummey

    Duration Formatted as Time on Y-Axis

    Steven Mullin

      Hey guys,

       

      I've been using Jonathan Drummey's calculation posted on his blog here - http://drawingwithnumbers.artisart.org/formatting-time-durations/

       

      Obviously it works very well.  I am, however, having an issue with a new viz.  This is actually the first time I've used a duration field on the Rows shelf, and when I do I get the below chart -

       

      Capture.JPG

       

      You can see that, even though the actual values are correct, the Y-axis is still displaying ticks between 60 mins and 1 hour.  No values actually exist in this range, they can't.

       

      Has anybody else come across this and found a way to fix it?

       

      Calc. for reference -

       

      INT(IIF([Duration] % 60 == 60,0,[Duration] % 60)// seconds
      + IIF(INT([Duration]/60) %60 == 60, 0, INT([Duration]/60) %60) * 100 //minutes
      + INT([Duration]/3600) * 10000) //hours
      

       

      This is formatted as a custom number (00:00:00) as per Jonathon's blog post.

        • 1. Re: Duration Formatted as Time on Y-Axis
          Jonathan Drummey

          Hi Steven,

           

          The technique I created is a workaround for displaying a duration in the desired format, it works by laying out a set of digits with just the right values that we can apply custom number formatting to it. When Tableau lays out a continuous (green pill) axis it either uses numbers (from 0-10 or log) or some part or level of dates where in that case Tableau understands that minutes and seconds go from 0-59 and then restart and so on. What's happening here is that Tableau is laying out the axis using number ranges with 60-99 minute durations that don't exist in the data because the calculated duration field you're using is technically a number, when what we'd really like is to use a date or duration (time) data type with the desired formatting.

           

          The workaround I've used in the past is:

           

          1) Use the original duration field as your continuous pill. Since this is the duration pre-formatting calculations then it will draw the correct position on the axis.

          2) Turn off Show Header for that axis and turn off tooltips for that duration field because those are non-sensical for the user.

          3) Use reference lines, bands, and/or mark labels to give enough information that the user can understand the axis range and make effective comparisons.

           

          Yes, this workaround is painful and difficult and ideally we wouldn't have to do all of this crazy calculation and setup. Vote up https://community.tableau.com/ideas/1054 and http://community.tableau.com/ideas/3315 to help prod Tableau into implementing this.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Duration Formatted as Time on Y-Axis
            Steven Mullin

            Thanks for the reply Jonathan Drummey.

             

            I hoped there might be yet another wonderful workaround, but hey-ho.  We'll just have to make do for now.

             

            I've upvoted those two ideas, though I don't hold out much hope for implementation given the length of time these have existed.  Fingers crossed though.

             

            I'll mark yours as the correct answer to keep the forums tidy, and thanks again for providing the knowledge that has gotten me this far.

             

            Might as well take this opportunity to give you two massive thumbs up for your 'Process Control Chart' post from way back.

            • 3. Re: Duration Formatted as Time on Y-Axis
              Gillian Murphy

              Thanks guys. I have gone through all of those posts but I'm afraid I'm struggling a little to get my head around them. I don't have a duration column or a means to get one. All I have is the time of day a job ended eg.

               

              28/04/2017 06:44:51

               

              Should I be converting this datetime to seconds (as per posts)?

              Thanks for help - appreciate it!

              • 4. Re: Duration Formatted as Time on Y-Axis
                Jonathan Drummey

                Hi Gillian, what's the start date/time? Assuming it's midnight then you can get a duration as a datetime using DATETIME(FLOAT([myDate])-INT([myDate])) and duration as seconds using (FLOAT([myDate])-INT([myDate])) * 86400. If you have two dates then you can get the duration as seconds using DATEDIFF('seconds',[startDate],[endDate]).

                • 5. Re: Duration Formatted as Time on Y-Axis
                  Jonathan Drummey

                  Aww, thanks!

                   

                  The Tableau Zen Masters asked the Tableau devs about duration/time support this past fall and the TL;DR on what we heard back was "it's complicated". So the devs know it's an issue but solving it will take awhile. Voting it up (and encouraging others to vote) is a good way to help keep the devs motivated to do the necessary work.

                   

                  Jonathan