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 

Please find the expected output based on the logic shared.

The workflow is built dynamically so it will work for huge datasets also.

Many thanks

Shanker V

[ryanzhang487]

Thank you very much Shanker, I am taking a look of it now,

I forgot to mention in the post, in real situation the date input matters too, in this sample dataset it is only 2022-Oct,

but in real situation I will need to review the worker efficiency and work type efficiency in monthly basis,

is there a way to include the year-month in the result? 

(I have changed my sample actual data a bit and added a few more rows for Nov and attached,

the standard table won't changed)

Thank you

[ShankerV · Accepted solution]

Hi @ryanzhang487 

The date can be incorporated in Summarize tool, for the group by option.

Will try to incorporate it in the workflow.

[ryanzhang487]

thank you very much for trying,

could you also see if you can add this to the logic? in case if there is a worker start on same step and end on the same step (for example start on C1 and end on C1), can you automatically set to use the next step time - the current step time (C2-C1) ?

is there a way to incorporate this to the workflow? thank you very much

[ShankerV · Accepted solution]

Hi @ryanzhang487 

Please find the result month wise.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @ryanzhang487 

As requested, can incorporate the change where C2 to C2 or C1 to C1 can be modified as C3-C2 and C2-C1 respectively.

But what will the value for the Key in worked hrs.

That will be key factor to do the calculations right?

Many thanks

Shanker V

[ryanzhang487]

yes the key in hrs won't change, it is just a special case that if a worker worked in the same step and end in the same step, we will automatically use (next step - current step) / key in hr, to calculate the efficiency

[ShankerV · Accepted solution]

Hi @ryanzhang487 

Hope you are expecting the modification like this.

In the Input the steps are same.

However for the calculation's have need to roll forward to next step so the calculation of efficiency will be accurate.

Many thanks

Shanker V

[ryanzhang487]

HI Shanker,

and if the start step is already the last step (for example start on C3 and there is no C4 to substract C3), we will auto just the start step to the step before last step (it should be start step as C2 and end step is C3), It could be worker input error,

could we incorporate in the workflow too?

sorry for adding a lot of requirements I sincerely appreciate your answer!