8 Replies Latest reply on Aug 17, 2015 1:55 PM by ailsa.zheng

    Difference in days between dates in a common field.

    Randy Hutterer

      Hello,

      working on a project to find the mean time between failures for my equipment, the data source only has one date field to work with is it possible to do this? here is a sample of the work sheet.

      sample of sheet.JPG

        • 1. Re: Difference in days between dates in a common field.
          Kassim Orra

          Hi,

           

          Can you send us a data example ?

           

          Your need is to show how many days the equipment work without a problem  ?

           

          regards

           

          Kassim

          • 2. Re: Difference in days between dates in a common field.
            Randy Hutterer
            GANG WORK_DATE REASON COMPONENT SUBCOMPONENT MACHINE_ID DIVISION SUBDIVISION DOWN_HOURS DELAY_HOURS
            TTPX0005 2/18/2015 HYDRAULIC HOSE SQUEEZE X6300171 GULF GALVESTON 0.5 0
            TTPX0005 2/19/2015 HYDRAULIC FITTING EXTRACT X6300236 GULF GALVESTON 0.4 0
            TTPX0005 2/19/2015 MECHANICAL CLAMP FRAME ROLLERS X6300102 GULF GALVESTON 0.4 0.4
            TTPX0005 2/26/2015 HYDRAULIC HOSE EXTRACT X6300102 GULF LAMPASAS 0.5 0.5
            TTPX0005 3/6/2015 MECHANICAL CLAMP FRAME ROLLERS X6300102 GULF LAMPASAS 2 2
            TTPX0005 3/9/2015 HYDRAULIC HOSE WORKHEAD X6300102 GULF LAMPASAS 0.5 0
            TTPX0005 3/10/2015 ELECTRICAL COIL BAD ORDER X6300102 GULF LAMPASAS 0.5 0
            TTPX0005 3/24/2015 OTHER OTHER OTHER X6300171 TEXAS FT WORTH 0.8 0.8
            TTPX0005 4/7/2015 OTHER OTHER OTHER X6300102 TEXAS FT WORTH 0.8 0
            TTPX0005 4/17/2015 OTHER OTHER OTHER X6300123 TWIN CIT WEST K O 0.5 0.5
            TTPX0005 4/21/2015 MECHANICAL CLAMP FRAME BROKE X6300102 TWIN CIT WEST K O 0.5 0
            TTPX0005 4/24/2015 HYDRAULIC VALVE WORKHEAD X6300236 TWIN CIT WEST K O 0.8 0
            TTPX0005 4/28/2015 MECHANICAL WORKHEAD MOUNTING X6300235 MONTANA EAST GLASGOW 0.3 0
            TTPX0005 4/28/2015 HYDRAULIC CYLINDER KICKER CYLINDER X6300236 MONTANA EAST GLASGOW 0.9 0
            TTPX0005 6/8/2015 HYDRAULIC CYLINDER JACK X6300102 TWIN CIT WEST K O 8 5
            TTPX0005 6/11/2015 HYDRAULIC HOSE RAIL CLAMP X6300102 TWIN CIT WEST K O 0.3 0.3
            TTPX0005 6/23/2015 HYDRAULIC CYLINDER EXTRACT X6300123 TWIN CIT WEST K O 2.3 0.9
            TTPX0005 6/24/2015 AIR BRAKES DIAPHRAGM X6300123 TWIN CIT WEST K O 0.2 0.2
            TTPX0005 6/24/2015 HYDRAULIC CYLINDER EXTRACT X6300235 TWIN CIT WEST K O 0.4 0.4
            TTPX0005 6/24/2015 HUMAN CAUSED OPERATOR HUMAN CAUSED OTHER X6300123 TWIN CIT WEST K O 0.7 0.7
            TTPX0005 7/2/2015 MECHANICAL CLAMP FRAME WHEELS X6300123 TWIN CIT WEST PROSPER 0.6 0.6
            TTPX0005 7/10/2015 OTHER OTHER OTHER X6300245 MONTANA EAST GLASGOW 1 1
            TTPX0005 7/13/2015 ELECTRICAL PROXIMITY SWITCH BAD ORDER X6300171 MONTANA EAST GLASGOW 0.7 0.7
            TTPX0005 7/16/2015 MECHANICAL CLAMP FRAME ROLLERS X6300123 MONTANA EAST GLASGOW 0.3 0.3
            • 3. Re: Difference in days between dates in a common field.
              Ben Page

              Hi Randy,

               

              I've come up with a solution that (I think) provides the correct value, but there is a key limitation, which I will explain below. To begin, I made one main assumption about the data, namely that "Work Date" represents the date of a failure.

               

              With that in mind I used two calculated fields to come up with my solution:

              differences in failures field.png

              I was having some trouble with getting what I wanted using Window calcs that computed along Pane (Down), so this field essentially provides a 0 for the first failure for each Machine ID, and does a DATEDIFF to give the difference for the rest of instances per Machine ID. The next is:

               

              Mean date diff field.png

              The denominator here is an LOD Calc that is a count distinct of the number of failures per Machine ID. I subtracted 1 because I assumed that you did not want the 0 assigned for the first failure for each ID to dilute the mean for each machine. The numerator represents the limitation to my solution: you must only have only Machine ID in the view to get the correct answer. You can see the result below.

               

              Mean date diff field 2.png

               

              Let me know if this is helpful,

              Ben

              • 4. Re: Difference in days between dates in a common field.
                Randy Hutterer

                Thank you for your help that works and I'm finishing up my Dashboard now. I really appreciate all the help!

                 

                Randy 

                • 5. Re: Difference in days between dates in a common field.
                  ailsa.zheng

                  Can you mark Ben's answer as Correct Answer Randy? It helps keep the forum more organized and also gives the answerer credit so they can collect badges

                  • 6. Re: Difference in days between dates in a common field.
                    Randy Hutterer

                    Will do glad you told me…only been using Tableau for 3 weeks.

                    • 7. Re: Difference in days between dates in a common field.
                      Randy Hutterer

                      Marked it as "Correct Answer" hope I did it correctly.

                      • 8. Re: Difference in days between dates in a common field.
                        ailsa.zheng

                        No no you have to mark Ben's answer as correct answer or else he doesn't get credit lol.