Is there a way to achieve this calculation logic? pls see the post for details

[ryanzhang487]

Hi community, 

I am finding a way to create a workflow in Alteryx to calculate the worker efficiency,

the left is standard table, the right is actual worker input table,

I want to compare the worker spent on the step indicated in the actual table (on the right), and compare to the standard step table (on the left), and calculate the worker efficiency (standard / actual worked) %,

Please click into my excel and there is a sheet with the detail logic explained,

Thank you for your help!

 

 

[ShankerV · Accepted solution]

Hi @ryanzhang487 

Yes when there is C4 that can be incorporated.

Please let me know whether the same to be incorporated for W4 also.

Edit: Also wanted to know whether they can input the C5 also by error.

Based on that, the workflow needs to be tweaked.

If C5, C6 different logic. Only C4 means, I can incorporate different logic.

Many thanks

Shanker V

[ryanzhang487]

yes so basically if the next row of [current accumulative standard time (hrs)] column is 0, then the last row is considered the 'last step', 

there will never be start C3 and end C3, and if that happens that is the table input error and we want to auto adjust it to be start C2 end C3 

 

[ShankerV · Accepted solution]

Hi @ryanzhang487 

Please find the amended workflow incorporate the input error.

Input was:

Amended Step start and Finish:

Many thanks

Shanker V

[ryanzhang487]

in real work the worker use the software to input start step and end step, there will not be a C5 or C6 because they are not exist and not available to choose in that software, so it wont be error, again thank you so much

[ryanzhang487]

hi Shanker thank you very much for actively answering my question again today I appreciate it,

For this part, is there a way to optimize it?

1, for example, in real situation the step names are not simply going in sequence like C1, C2, C3, it will be like CL1/1(step 1), CTSD12(step 2), CDD (step 3), so taking the last number and add 1 does not work, it will have to link to the standard table to find out what next step is before joining

2, if start step = finish step =  last step, then the start step will auto adjust one step back, (in the workflow you created if "W3" then "W2" because we know W3 is the last step), but in real situation we don't know if start step is the last step, the way to know this is to link to the standard table when the below row's accumulative time = 0

I hope that make sense and thank you again