Start counting after certain value

[NatasjavdHengel]

Hi everyone,

I need some help with Alteryx. For a client I need to check whether every workflow (having a unique doc. nr) has two or more different people involved. I worked that out but now the client also wants to know this amount after a certain action has been performed.

So as you can see for doc. nr. 417 it is the same person who performed several tasks in the workflow, but for doc.nr. 9746 there are multiple people involved.

What I have now is that Alteryx calcultates per unique doc. nr. the amount of different people involved. Now the client wants to know this as well but now it has to start counting, in case a workflow involves a task "send back" (or something like that), after the task "send back" to get better insights. So for 900033 is has to be 2 instead of 4 different persons (So send back (Tom) not included as well).

Who can help? Thanks!

Record #	Workflow	Name employee	Doc. nr	Task
1	53	Mart	417	Register
2	53	Mart	417	Completed
3	900033	Sebastian	9746	Register
4	900033	Marie	9746	Completed
5	900033	Sebastian	9746	Completed
6	900033	Tom	9746	Send back
7	900033	Marie	9746	Sended again
8	900033	Tessa	9746	Completed
9	1101	Patty	101	Make new form
10	1101	Patty	101	Register
11	1101	Mark	101	Completed form
12	1101	Patty	101	Completed

[wdavis]

Hi @NatasjavdHengel 

Will your Tasks always be in this order? So that 'Send Back' as a task will always come after 'Register' and 'Completed'?

If that is the case, we can use a Multi-Row Formula to assign a value to each task indicating whether it should be counted or shouldn't.

Create a new field for this and the Formula would be;

IF [Task] = "Send Back" Then 1

ELSEIF [Row-1:NewField] = 1 Then 1

Else 0

ENDIF

You can then add this as a groupby in your Unique Tool (or method you currently use)

Let me know if this works for you, or need some more assistance on this!

Thanks

Will

[NatasjavdHengel]

Hi @wdavis 

No, often "send back" or "send back ... (several options like form, docs) are not in a specific order after register and completed. Could be anything before it.

I am also not sure whether your solution makes sure now that in case of the example row 4 and 5 are also not counted anymore. 

I added a workflow with the example --> before the summarize tool there has to be done something. Hopefully you can help!

[wdavis]

Hi @NatasjavdHengel 

Would you also be able to upload the excel input for the workflow please and I'll take a look!

Will

[NatasjavdHengel]

@wdavis No problem!

[wdavis]

Hi @NatasjavdHengel 

Unless we are able to find something unique within the dataset to identify what is Before and what is After it may be hard to achieve this in Alteryx.

I have updated the workflow below which looked to assign group values based on the task name. However, I realised that 'Completed' occurs both in stage 1 and 2 (if sent back).

If you were able to get the data formatted prior to Alteryx so that it was ordered by task status as mentioned above this would be made possible.

Does that make sense?

Thanks

Will

[NatasjavdHengel]

Hi @wdavis ,

Thanks for trying!

In the original data file, another column is "chosen action in workflow" which involves the text "not okay, sended back" in case the task is "sended back". After sended back, in this column "chosen action in workflow" it mentions "changes, sended again" and "solved". 

However, "solved" is also used in case nothing has been sended back.

As the file consists of more than 100.000 rows, it will be hard to format it prior to Alteryx by hand.

I assume this will be too hard to achieve in Alteryx then.

[wdavis]

How do you currently achieve this output for your client? 

Because I think the logic to not count any of the tasks around 'Send Back' is easy to implement. The tricky part is being able to determine which 'Completed' action we want to be including in the distinctive count.

If there is a way you are currently doing this, we may be able to replicate it in Alteryx though

Will

[NatasjavdHengel]

Well I received the output excel file from the client; one consists of 41.000 rows and the other one almost 500.000 rows. As there is no unique identifier to mark the actions after sended back, it would be probably be done by hand. 

As attachment I added a small piece of the original output file (in Dutch unfortunately).

Dos.item 8449 has the action type "afkeuren declaratie" --> which means sended back. After that the different employees need to be counted.

I don't know whether you are able to replicate something in Alteryx, otherwise I will communicatie to my manager that it is simply not able to be done in Alteryx.

[wdavis]

Hi @NatasjavdHengel 

Based on the new dataset I think this is possible to do. If we use the 'End' field to determine the time of each of the actions, we can assume (correct me if I'm wrong) that all of the actions after that period would be excluded.

This means we can now create a group based on the name of 'Afkeuren declaratie' using the Mult-Row Formula. It firstly identifies any row that has this as an action type and assigns it a 1. For any subsequent rows in the same 'Dos.Item' it will then also label these with a 1.

You can then use a Filter tool to remove these values, and just perform the Summarize on actions that have not been sent back.

Does this work?

Will