9 Replies Latest reply on Apr 7, 2017 9:51 AM by Karthik Venkatachalam

    DD:HR:MI as Reference Line tooltip

    Karthik Venkatachalam

      Hi,

      I have a requirement where I have to show "Actual Time taken" for workflow steps, against "Expected Time Taken". Data for both fields are stored in minutes. And I am able to  convert it and put it in easy to grasp into DD:HR:MI string label. However, I am unable to use them as a measure. Only able to use Minutes field as a measure. Since I am using bullet chart, Reference lines do not show as DD:HR:MI string, instead they show as Minutes.(See the screenshot).

      Question is, How can I show reference line tooltip as d:h:m instead of 11,520 minutes?.

      Currently there is no option for tooltips for reference line currently. Is there any other workaround to have both bullet charts and Display reference line tooltip as DD:HH:MI?

       

      Attaching a sample workbook V10.0.2

       

      Thanks much!

        • 1. Re: DD:HR:MI as Reference Line tooltip
          Galen Busch

          Hi Karthik,

           

          I took a look at your workbook and have a 'sort of' answer.

           

          It's sort of tricky what you're asking Tableau to do. Firstly, I took your Time to ship minutes and changed the calculation to a datediff() for simplicity. This returns the same number:

           

          datediff('minute',[Order Date],[Ship Date])

           

          The result in either instance is a numeric value. For each record, we return a scalar value for the difference between order date and ship date.

           

          To get from this scalar value to a prettier version, you did some string transformation to get to your dd:hh:mm format. This is a string value that is unique for each row. You can't (as you well know) average a string value, what's the average of apple and orange? Well, there isn't one.

           

          I tried looking at Reference line>format>numbers>custom, to put in dd:hh:mm which Tableau recognizes. However, it is translating an averaged value (2880 for example) into a dd:hh:mm format, which it attempts to turn into a single date, not date difference value. Unfortunately, this question has been asked a few times. I worked through this issue a few weeks back when trying to change a reference line for a big deliverable to not display as "sum of sum of avg goal line goal line" to something a little simpler.

           

           

          Sorry I couldn't be of more assistance, I don't believe it to be possible at this time.

           

          Galen

          • 2. Re: DD:HR:MI as Reference Line tooltip
            Galen Busch

            Karthik,

             

            I tried this a different way and got the result I believe you were looking for.

             

            1. I created a second calculation to label [Time to ship minutes] with the formula:

            STR(INT(AVG([Time to Ship Minutes])/1440))

            + "d:" +

            STR(INT(AVG([Time to Ship Minutes])/60%24))

            + "h:" +

            STR(INT(AVG([Time to Ship Minutes])%60))

            + "m"

             

            2. I moved SUM([Expected time to ship Minutes]) to Columns, right clicked>dual axis

             

            3. Right click on the dual axis, and 'Synchronize axis'

             

            4. Remove reference line

             

            5. Drag your new calculation from step 1 to the label for SUM([Expected time to ship Minutes])

             

            Karthik.JPG

            6. You can then filter out your pane bands

             

            7. I might recommend making the expected time to ship more transparent

            1 of 1 people found this helpful
            • 3. Re: DD:HR:MI as Reference Line tooltip
              Kaz Shakir

              Karthik,

              I'm a little confused by your viz.  If you are trying to show how long it takes to process an order vs. the expected time to process an order, and your expected time for all orders is 2 days (or 2880 minutes), then your reference line should be the same for each order and not moving.

               

              If my understading is correct, then I think you need to change the formula in your calculated field called "Expected time to ship_DDHHMM), to look like this:

              (and you should probably change the name to be Actual time to ship_DDHHMM)

              Then, you need to change the aggregation on the Expected time to ship Minutes field to AVG:

              Then change your refernce line to also use average:

              Then you viz should look like this:

               

              This doesn't address your issue of the tooltip for the reference line.

               

              Kaz.

              • 4. Re: DD:HR:MI as Reference Line tooltip
                Karthik Venkatachalam

                Thanks Galen!

                Using stacked/dual axis is a great work around. I never thought of it. Very clever!

                However, we have to think about this, as our users are used to seeing "stacked" (dual axis) in a different context. Your solution is very practical. I will play with it and see if I could get it to where we want.. Thanks again for a nice workaround!

                 

                • 5. Re: DD:HR:MI as Reference Line tooltip
                  Karthik Venkatachalam

                  Thanks Kaz, In my production scenario, my reference line is pulled from another column and its not a constant. Though it might appear as constant in the Superstore sample I attached.

                  • 6. Re: DD:HR:MI as Reference Line tooltip
                    Galen Busch

                    Glad to help, Karthik. If you feel my answer correctly solved your problem, please 'Mark as correct answer' so other users with similar problems can find this solution in the future.

                     

                    Kindly,

                     

                    Galen

                    • 7. Re: DD:HR:MI as Reference Line tooltip
                      Karthik Venkatachalam

                      Thank you Galen, Though yours is a nice workaround, I would like to keep the thread open for any possible answers that are closer to my requirement. Lets see.

                      • 8. Re: DD:HR:MI as Reference Line tooltip
                        Galen Busch

                        Hi Karthik,

                         

                        The ability to edit reference line text is not a current functionality in Tableau as described here:

                        Edit Reference Line Tooltip Text | Tableau Software

                         

                        If you'd like to upvote the feature, vote here:

                        https://community.tableau.com/ideas/2235

                         

                        If you're looking for other workarounds, let me know what other options you'd like to explore

                        • 9. Re: DD:HR:MI as Reference Line tooltip
                          Karthik Venkatachalam

                          Thanks much Galen!!

                          I took your suggestions and changed the formatting a bit, to get it close to where I wanted. Its not quite 100%, but lets say 97%.   We can definitely tell the users to keep the teal bar within the light gray one Yours looked like a stacked bar, hence I had some reservations yesterday. But changing the size of these 2 bars, and changing transparency got it closer to what we wanted. I have also up voted the Idea you have posted in here for the reference line tool tip. Thanks again for your great effort!  I am marking it answered.