Skip to main content
Topics#TasksPregunta de Tableau Community Forums Member (Inactive)
Tableau Community Forums Member (Inactive) (Salesforce) preguntó en #Tasks
5 sept 2019, 21:10

Calculation Based on Sequence

Background:

A document requires reviews to happen by specific reviewers in a specified timeframe. In order to observe bottlenecks, analyzing how long a specific reviewers take to review is paramount. There are two different review "environments" or workflows.

  • One is a Sequential workflow, where once the document is Issued (captured in Issue Date), the first reviewer's clock starts. Once that reviewer closes their task (captured in Closed On) then the second reviewer's clock starts, and so on.
  • The other is a Parallel workflow, where once the document is issued, then everyone can simultaneously review the document - i.e. everyone's clock starts until they close their task.

 

The parallel workflow is an easy capture, I can create a calculated field that basically says, "If the workflow type is parallel, then use the DATEDIFF function using the Issue Date and the Closed On date to find the time to review." Boom, done.

 

But, the sequential workflow is a little more complicated (but hopefully not for you reading this post). There are two different calculations that need to happen, and I can do this easily in an Excel sheet, but that is not where I am pulling the data from. Essentially if:

  • The reviewer is the first in the workflow (Task Sequence = 0), then use DATEDIFF with the Issue Date and Closed On date - easy.
  • The reviewer is the 2nd, 3rd, 4th, nth reviewer, then I need to use the DATEDIFF function with the Closed On date of the (n-1) task sequence and the Closed On date of the nth task sequence.

 

Example:

Issue DateReviewer NameClosed On Workflow TypeDurationNotes8/11/19Joe8/14/19Sequential3DATEDIFF('day', Issue Date(Joe), Closed On(Joe))8/11/19Sam8/16/19Sequential2DATEDIFF('day', Closed On(Joe), Closed On(Sam))8/11/19Jess8/22/19Sequential6DATEDIFF('day', Closed On (Sam), Closed On(Jess))8/11/19Michelle8/25/19Sequential3DATEDIFF('day', Closed On(Jess), Closed On(Michelle))

 

What do you guys think? Would love to be able to make this happen.

 

In the end it will all combine into a large IF statement to keep it all (parallel and sequential) in one column - oh and make it so that it is only working days, too .

 

Using Tableau Desktop 2018.3.

Duration Calculation_1.0.twbx
18 respuestas
  1. Tableau Community Forums Member (Inactive) (Salesforce)
    6 sept 2019, 08:07

    HI Henry

     

    Thanks for attaching a packaged workbook & providing the version no - makes things a lot simpler :-)

     

    Based on the example data in your workbook, I've created a Review Start Date using table calculations :

     

    HI Henry Thanks for attaching a packaged workbook & providing the version no - makes things a lot simpler :-) Based on the example data in your workbook, I've created a Review Start Date using table c

     

    Review Start Sate : DATE(IF ATTR([Task Sequence]) = 0 THEN MIN([Issue Date]) ELSE LOOKUP(MIN([Closed On]),-1) END)

     

    What this is basically saying is "If the task sequence is the first one (task sequence = 0) , then use the issue date else use the closed on date of the row before.

     

    As we're using table calculations, the information needs to be aggregated, hence the ATTR, MIN functions.

     

    I think this should give you what you then need to be able to DATEDIFF between Review Start Date and Closed On date.

     

    Workbook with the above attached in v2018.3.

     

    Hope that helps you get you on  your way.

     

    Regards

    Donna

Escribir una respuesta...
0/9000