11 Replies Latest reply on Apr 4, 2014 2:48 PM by Noah Salvaterra

    How do I turn time column Dimensions into Measures?

    Jean Jacques Potgieter

      I have a lot of Measures and I want to be able to create a chart and filter by Measure Names. The problem is that the the Measures that are related to time are turned into Dimensions so they don't show up under the Measure Names. How would I get them to show?

       

      They're formatted as Custom in excel and they look like this:

       

      0:23:38
      0:21:11
      0:14:05
      0:13:10
        • 1. Re: How do I turn time column Dimensions into Measures?
          Noah Salvaterra

          I tried pasting this list into Tableau and it recognized them all as date-times. If that happened for you, great, if not try right clicking the field in the sidebar and select "Date & Time" under "Change Data Type". Now you can convert this to a single number using datepart as follows:

           

          datepart('hour',[time])*60^2+datepart('minute',[time])*60+datepart('second',[time])

           

          This will create a measure which is the number of seconds since midnight. I trust you could convert that to whatever units you need.

           

          Let me know if this works for you. If not please post a sample .twbx with some data formatted like yours. You can attach a file in the advanced editor which you link to from the top right corner of the reply box.

           

          N.

          1 of 1 people found this helpful
          • 2. Re: How do I turn time column Dimensions into Measures?
            Jean Jacques Potgieter

            Sorry, I'm pretty new to Tableau. I can't find where to put the datepart formula. Would you create a calculated field?

             

            Also those numbers don't represent a time of the day per se, they're the length of time someone was on the phone or how long it took to complete a task. Not sure if this clarification matters.

            • 3. Re: How do I turn time column Dimensions into Measures?
              Noah Salvaterra

              Right click on a field in the white area of the sidebar and select "create calculated field". Replace whatever pops up in the window with a version of the above (replacing time with your date-time dimension).

               

              I don't think the distinction matters. Converting these to date-time will associate some date (probably something like 1/1/1899). Seconds from midnight on whatever date should be the same as the original time. This approach got around an issue I was worried about where the hour might be 2 digits. If that isn't ever the case you could also use:

              int(left([time],1)*60^2+int(mid([time],3,2))*60+int(right([time],2))

              I think this would agree with the approach above.

              1 of 1 people found this helpful
              • 4. Re: How do I turn time column Dimensions into Measures?
                Jean Jacques Potgieter

                The first calculation gives me the seconds and works great! I'm currently trying to convert it to hours:seconds. The second calculation contains an error because a parenthesis was left off the int(left(...) and when I tried adding another, I got errors for left, center, and right expecting a (String, float) instead of (datetime, integer)

                 

                There are some fields (the totals) that have a value of

                 

                932:12:58
                642:03:37

                 

                (932 days: 12 hours: 58 minutes)

                • 5. Re: How do I turn time column Dimensions into Measures?
                  Noah Salvaterra

                  Yeah, missing parenthesis, that is what happens when I write calcs without testing them. it also assumed the field was a string, you could change the type as above.

                   

                  Not sure I understand the form you are trying to convert to. I think the best I can do is Hours.Stuff where stuff is fractional stuff (minutes and seconds) that doesn't add up to a whole hour. for that just divide either of the second calculations by 60^2

                   

                  You could do a similar formula to the second one for the totals parsing out the individual fields:

                  int(left([time],3))*24*60+int(mid([time],5,2))*60+int(right([time],2))


                  if days isn't always 3 digits you may need to get a bit trickier and find the first ":"

                  int(left([time],find([time],":")-1))*24*60+int(mid([time],find([time],":")+1,2))*60+int(right([time],2))


                  Note, I just left those in minutes as I assume you'll be converting to hours or days.

                  • 6. Re: How do I turn time column Dimensions into Measures?
                    Jean Jacques Potgieter

                    Thanks a lot for all the help, I really appreciate it! I didn't know that datepart existed and I'm very grateful for the helpful instructions.

                    • 7. Re: How do I turn time column Dimensions into Measures?
                      Noah Salvaterra

                      No problem. Glad I could help.

                       

                      The calculation editor includes some really handy reference material that I've noticed a lot of people don't seem to see at first (it is overwhelming to start creating your own formulas). All of the functions are listed out, and there is a drop-down for a few larger categories (so you can see functions that have to do with dates for example). Note the yellow box on the bottom right as well, if you click on a function in the formula window it will bring up help that relates to that function.

                       

                      N.

                       

                      Calculation Editor.png

                      1 of 1 people found this helpful
                      • 8. Re: How do I turn time column Dimensions into Measures?
                        Jean Jacques Potgieter

                        I used your formula: int(left([time],find([time],":")-1))*24*60+int(mid([time],find([time],":")+1,2))*60+int(right([time],2))

                        and it shows the minute value of the field, but I want it to look like the original field but in a Measure so it's filterable using the Measure Names pill. I don't want to change the formatting, I still want it to show up as 00:00:53 or 932:12:58. Here is the worksheet with dummy data so you can see what I mean. I want the first sheet to have the data from the second sheet as a measure so that on the dashboard I can filter it.

                        • 9. Re: How do I turn time column Dimensions into Measures?
                          Noah Salvaterra

                          You can't always get what you want. But you can get these values into a table.


                          I included a trick I see a lot for getting things together into a table that might not work in measure values. Basically the idea is to use zero to plot text, so the field you're interested in is actually on the labels shelf. This does require a bit of extra attention regarding layout, i.e. editing the axes so they don't show the tick marks and the axis label shows corresponds to the label field (instead of zero). I did the first couple to demonstrate that technique. The most common reason for doing this is to allow for conditional formatting within each column, but hopefully it works for your case as well.


                          N.

                          1 of 1 people found this helpful
                          • 10. Re: How do I turn time column Dimensions into Measures?
                            Jean Jacques Potgieter

                            My trial of Tableau ran out yesterday so apparently I can't open that. When I try opening it with Tableau Public, it keeps telling me that I have a newer version and it just opens my worksheet. My company is in the process of buying it but we don't have the licenses yet.

                            • 11. Re: How do I turn time column Dimensions into Measures?
                              Noah Salvaterra

                              Check with your Tableau sales rep. If your company is working on a purchase they may be able to get a temporary license key. That would keep you going for another week or two.