6 Replies Latest reply on Sep 30, 2015 10:39 AM by Cristina Cruz

    Date - Time difference between activity type appearing multiple times

    Cristina Cruz

      Hi,

       

      I'd like to calculate the time difference between activity TYPE, say time it takes from  type= open to type = assignment. If assignment type appears only onces, this calculated field for returning assigned date works fine: IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN [Datestamp] END

       

      and Open to Assigned calculated will work fine too: DATEDIFF('hour', ([Tickets open by IT Service Desk]),([Assigned Dates]))

       

      However, my data shows that assignment type can occur multiple times at most 3x.

       

      What I am thinking is to created 3 types of Assignment calculated field such as Assigned Date 1 (first time assignment occurred), Assigned Date 2 (2nd time assignment occurred) and Assigned Date 3 (3rd time assignment occurred) but not sure how to modified this calculation: IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN [Datestamp] END

       

      Can you or anybody help?

        • 1. Re: Date - Time difference between activity type appearing multiple times
          Cathy Bridges

          I'm not 100% sure this will work, without seeing your Tableau workbook, but you could try adding MIN to your calculation.

           

          IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN MIN([Datestamp]) END

          • 2. Re: Date - Time difference between activity type appearing multiple times
            Cristina Cruz

            Hi,

             

            I tried doing that but i get this error:

            • 3. Re: Date - Time difference between activity type appearing multiple times
              Daniel Vincent

              Put this instead:

               

              IF ATTR([Type]) = 'Assignment' AND ATTR([Tier Level]) = 'EPM Tier 3' THEN MIN([DateStamp]) END

              • 4. Re: Date - Time difference between activity type appearing multiple times
                Rody Zakovich

                Or you can use an LoD to make it row level.

                 

                { MIN( IF [Type] = 'Assignment' AND [Tier Level] = 'EPM Tier 3' THEN DateStamp] END) }

                 

                Not sure how you intend to layout your Viz, but you can swap this with INCLUDE/EXCLUDE to avoid adding Filters to CONTEXT.

                 

                Regards,

                Rody

                • 5. Re: Date - Time difference between activity type appearing multiple times
                  Cathy Bridges

                  Ah! Thank you, Dan - I always forget about adding ATTR!

                  • 6. Re: Date - Time difference between activity type appearing multiple times
                    Cristina Cruz

                    Hi Everyone,

                     

                    Thanks for replying. Let me take a step back an explain what I am trying to do here. I basically am trying to create a cycle time viz similar to this: | Tableau Public

                     

                    My data is structured this way:

                          

                    INCIDENT_IDOPEN_TIMECLOSE_TIMETIER LEVELDatestampType
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 8:05Assignment
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 7:55Assignment
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 17:29Assignment
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 10:14Attachment moved
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/17/2015 13:58Closed
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 39/11/2015 9:39Closed
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/17/2015 13:58Communication with customer
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 39/11/2015 9:27Communication with customer
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 39/11/2015 9:27Communication with customer
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 39/11/2015 9:32Communication with customer
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/19/2015 10:14DEADLINE ALERT
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 10:14Open
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 17:28Operator update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/13/2015 10:42Operator update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 7:55Operator update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 17:28QoS Update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 17:28Reassignment
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 7:55Reopen
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 8:05Update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/18/2015 7:55Update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/12/2015 17:29Update
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/17/2015 6:13Update from customer
                    IM10978958/12/2015 10:149/11/2015 9:39EPM Tier 38/19/2015 9:14alert stage 1

                     

                    What I initially did is to create a calculated field to return the date stamps per TYPE = Assignment with this formula: IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN [Datestamp] END

                     

                    However, the issue is that, in a single incident ID, each type can show up multiple times. So, that's when I thought of getting the MIN, Median, and Max but not sure if that is the best approach.

                     

                    Does anyone have any suggestion on how I can use my data set without transforming or replicating the data?

                     

                    Tina